Excel has many built-in financial functions. For the purpose of this introductory exercise, we will study only a few functions.
FV () Function
This function is used to calculate the future value of an investment
Calculate the future value of an investment with a 12 percent interest rate.
In a worksheet, type the following:
In cell A5, the formula is =FV(A2/12, A3, A4). The annual interest rate is divided by 12 because it is compounded monthly.
PV() Function
This function returns the present value of an investment.
Suppose you’re thinking of buying an insurance annuity that pays Rs 500 at the end of every month for the next 20 years. The cost of the annuity is Rs 60,000, and the money paid out will earn 8 percent. You want to determine whether this would be a good investment.
The result is negative because it represents money that you would pay, an outgoing cash flow. If you are asked to pay (60,000) for the annuity, you would determine this would not be a good investment because the present value of the annuity (59,777.15) is less than what you are asked to pay.
Cell A5 has the formula =PV(A3/12,12*A4, A2,, 0 )
The interest rate is divided by 12 to obtain a monthly rate.
PMT() Function
This function calculates the payment for a loan based on constant payments and a constant interest rate.
Calculate the amount to save each month to have 50,000 at the end of 18 years.
In cell A5, type the formula =PMT(A2/12,A3*12,0, A4 )
The interest rate is divided by 12 to obtain a monthly rate.
IPMT() function
This function returns the interest payment for a given period for an investment based on periodic, constant payments and a constant interest rate.
Type the following data in a worksheet:
Cell A6 contains the formula =IPMT(A2/12,A3*3,A4,A5)
Cell A7 contains the formula =IPMT(A2,3,A4,A5)
NPER() function
This function returns the number of periods for an investment based on periodic, constant payments and a constant interest rate.
Cell A7: =NPER(A2/12,A3,A4, A5, A6 )
Cell A8: =NPER(A2/12,A3,A4, A5 )
Cell A9: =NPER(A2/12,A3,A4)
RATE() function:
Returns the interest rate per period of an annuity.
Cell A5: =RATE(A2*12, A3,A4 )
Cell A6: =RATE(A2*12, A3,A4 )*12
NPV () Function:
Calculates the net present value of an investment by using a discount rate and a series of future payments (negative values) and income (positive values).
Cell A7: =NPV(A2,A3, A4, A5, A6 )
IRR() Function
Returns the internal rate of return for a series of cash flows represented by the numbers in values. The cash flows must occur at regular intervals, such as monthly or annually. The internal rate of return is the interest rate received for an investment consisting of payments (negative values) and income (positive values) that occur at regular periods.
Cell A8: =IRR(A2:A6)
Cell A9: =IRR(A2:A7)
Cell A10: =IRR(A2:A4, -10% )
