One of the more conservative investment strategies available is to purchase an instrument such as a certificate of deposit or fixed-rate annuity. You can evaluate this type of investment using the FV, or future value, function.
- [Instructor] One of the more conservative investment strategies is to purchase an instrument such as a certificate of deposit or a fixed-rate annuity that enables you to trade lower risk for a relatively low, but known, rate of return. You can evaluate this type of investment using the future value, or FV, function. I'll demonstrate how to do that in this movie. My sample file is the future value workbook, and you can find it in the chapter one folder of the exercise files collection. The FV function can take up to five arguments.
The first is the rate, and that is the annual interest rate. You will often divide that by the number of times the interest is compounded during the year. In this case, we'll assume that interest is compounded monthly. Next, we have the number of periods. If we're assuming a monthly compounding, then 120 periods is 120 divided by 12, or 10 years. Next is the payment, that would be the amount of money that you pay into the investment on a regular basis.
So for example, if you were to pay 5,000 every month, the payment is expressed as a negative number because it is an outflow from your account. Same thing is the present value. It's a negative 150,000, and again, we're assuming that you have paid $150,000 to start the investment. Finally, we have type, and type can be either zero or one. For type zero investments, we assume that interest will be compounded at the end of a period, whereas for a type one, interest will be compounded at the beginning of a period.
Zero is the default, and if you leave that argument blank, then Excel will assume that the value is zero. So let's see how this works in a formula. I'm in cell B nine, so I'll type equal, and then FV for future value. And then the rate is in cell B three. And remember, we need to divide that by 12 because we're compounding monthly. Then a comma, number of periods is B four, comma. Payment, which is currently zero, is in B five.
Comma, then present value of minus $150,000 is in B six. I should note that payment, or present value, can be zero, but not both. So that means if you don't have a payment, you must have a PV. Or if you do not have a present value, then you must have a payment. And they can also be other numbers as well. But if one is zero, the other can't be. And then finally type is in B seven. So we'll assume end of the period for zero. Right parentheses, and enter.
And we get a future value of $247,051.42. Just by way of demonstration to show how much difference it makes when you change when interest is compounded, that is, at the beginning or the end of a period, the FV function offers investors a straightforward means of evaluating a fixed-rate investment. What's more, it gives analysts the ability to evaluate annuities where the beneficiary receives periodic payments.
- Calculating the effect of interest rates and inflation
- Finding the arithmetic and geometric means of growth rates
- Calculating the future and present value of an investment
- Calculating loan payments for a fully amortized loan
- Calculating the effect of paying extra principal with each payment
- Finding the number of periods required to meet an investment goal
- Calculating net present value and internal rate of return
- Building a cash tracking worksheet
- Visualizing cash flows using a waterfall chart