The Future Value (FV) Function in Excel
To compute the future value using Excel, we need to use the FV function. The parameters are:
Rate: Rate is the periodic interest rate.
Nper: Nper is the number of periods.
Pmt: Pmt stands for the periodic payment, and is not applicable in this case because there are no periodic cash flows. Thus, we can either put a zero, or an extra comma in lieu.
Pv: Pv stands for the present value, or the initial investment. We input it with a negative sign in order to ensure that the answer is positive. In many Excel functions, cash flows in one direction are positive while those in the opposite direction are negative. Thus, if the investment is positive, the subsequent inflow is negative, and vice versa. In this case, if we specify a negative number for the present value, we get the future value with a positive sign. If, however, the present value is given with a positive sign, the future value, although it would have the same magnitude, would have a negative sign.
Type: This is a binary variable, which is either 0 or 1. It is not required at this stage, and we can just leave it blank.
EXAMPLE 2.24
Rosalyn has deposited $20,000 with a bank for five years. The bank has agreed to pay 4.8% interest per annum compounded annually. How much can she withdraw at the end?
We will invoke the function as, FV(.048,5,,−20000) and the answer is $25,283.45. In this function we are inputting an extra comma in lieu of the value for Pmt. As an alternative we could have given the value as zero.
Now assume that the bank is quoting a rate of 4.8% per annum with quarterly compounding. The periodic interest rate is 1.20%, and the number of quarterly periods in five years is 20. The future value may be computed as follows.
The Present Value Function in Excel
The required function in Excel is PV. The parameters are:
Rate
Nper
Pmt
Fv
Type
Fv stands for the future value. The other parameters have the same meaning as specified for the FV function.
EXAMPLE 2.25
Sharon Oliver wants to accumulate $25,000 in her bank account after five years. The bank agrees to pay 5.40% per annum compounded quarterly. How much should she deposit today?
COMPUTING THE PRESENT AND FUTURE VALUES OF ANNUITIES AND ANNUITIES DUE IN EXCEL
EXAMPLE 2.26
Allegra is offering an instrument that promises to pay $4,000 per year for 10 years, beginning one year from now. If the annual rate of interest is 5.40%, and interest is paid annually, what is the present value of the annuity?
We can use the PV function in Excel. The parameters are: Rate = 0.054, Nper = 10, Pmt = –4,000. There is no need to input parameters for Fv and Type. This is because there is no lump-sum terminal cash flow, and so there is no need to input a value for the future value. Type needs to be input only for annuities due.
The future value of this annuity may be computed using the FV function.
Now assume that the above annuities are annuities due. The present and future values may be computed as follows.
And
AMORTIZATION SCHEDULES AND EXCEL
Lorraine has taken a loan of $500,000 which has to be paid back in eight annual installments. The interest rate is 4.80% per annum. The periodic installment can be computed using the PMT function in Excel. The parameters are:
Rate
Nper
PV
FV
Type
The values for PV and FV should have opposite signs.
For the first period,
Now consider the second period. There are two ways in which the PMT function can be invoked. We can specify the same set of parameters as for the first period. Or we can specify the Nper as 7, and the PV as the outstanding balance, which is $447,263.34.
Now consider the interest and principal components of each installment. We can use a function in Excel called IPMT to compute the interest component of an installment and another function called PPMT to compute the principal component of the installment. The parameters, for both, are
Rate: This is the periodic interest rate.
Per: This stands for period.
Nper: This represents the total number of periods.
Pv: This is the present value.
Fv: This is the future value.
Type: This has the usual meaning.
Consider the interest and principal components of the first installment.