Page 2 of 3
As well as helping you choose the correct function there is also help in correctly specifying each of the items of information it requires - its parameters or, to use the correct terminology, the arguments.
Once you click OK the Function Arguments dialog opens. Notice that it provides information on each of the arguments that you need to enter and that the mandatory ones are in bold while the optional ones are not. You can either type directly into each box or you can use the icon to the right of each one to select the appropriate cell from the spreadsheet.
Rate is the first box and the rubric reads "Rate is the interest rate per period". As we have specified monthly income the period in this example is month, i.e. 12 times per year.
We'll use the selecting cell method to enter the argument into the Rate box so click on the icon at the right of the box, the click on cell B4, type "/12" in order to convert from the annual to the monthly rate and click on the icon to return to the Function Arguments dialog.
Moving the cursor into the Nper box the rubric reminds us that "Nper is the total number of payment periods in the investment". So the number of yearsin B3 needs to be multiplied by 12 to convert it to months.
Finally place the cursor in the Pmt box and read that "Pmt is the payment made in each period ...". Type or select B5 to fill this box and then click on OK.
When you press OK the value of the annuity is shown as negative because this is the amount of money you would have to pay out now at the assumed interest rate to get the same as the annuity is paying you.
With the figures we have selected the value of the annuity is slightly less than the purchase price. The actual difference between the two values can be calculated with a formula that subtracts what you have received from what you have paid. Enter the label:
Difference in B7
and the formula:
=B2-B6 in B7
You can easily apply currency formatting with the format painter.