ANNUITIES IN EXCEL

How to calculated  Future value annuity & 

Present value annuity in Excel 2007???

Microsoft Excel as a Financial Calculator Part II

In the previous section we looked at the basic time value of money functions and how to use them to calculate present and future value of lump sums. In this section we will take a look at how to use Excel to calculate the present and future values of regular annuities and annuities due.

A regular annuity is a series of equal cash flows occurring at equally spaced time periods. In a regular annuity, the first cash flow occurs at the end of the first period. When using the TVM functions with regular annuities, either omit the Type argument or set it to 0.

An annuity due is similar to a regular annuity, except that the first cash flow occurs immediately (at period 0). When using the TVM functions with annuities due, you must use the Type argument and it should be set to 1.

Example 2 — Present Value of Annuities

Suppose that you are offered an investment that will pay you $1,000 per year for 10 years. If you can earn a rate of 9% per year on similar investments, how much should you be willing to pay for this annuity?

In this case we need to solve for the present value of this annuity since that is the amount that you would be willing to pay today.

Recreate the spreadsheet pictured above, but leave B5 empty for now. To calculate the present value of an annuity (or lump sum) we will use the PV function. Select B5 and type: =PV(B3,B2,B1). The answer is -6,417.66. Again, this is negative because it represents the amount you would have to pay (cash outflow) today to purchase this annuity.

Usually, I would like this to show as a positive number (even though it represents an outflow), so I would put a negative sign in front of the function. It would then look like: =-PV(B3,B2,B1) and would give the same answer, except that it would be a positive number. Note that I entered the annuity payment reference (B1) as a positive number because the problem specifically said that you would be receiving this amount each year (a cash inflow).

Example 2.1 — Future Value of Annuities

Now, suppose that you will be borrowing $1000 each year for 10 years at a rate of 9%, and then paying back the loan immediate after receiving the last payment. How much would you have to repay?

In this case, we want to find the future value of the annuity. In your worksheet, change the label in A5 to Future Value and then in B5 enter: =FV(B3,B2,B1).

Note that the order of the arguments in both the PV and FV functions are identical, so you could have just changed the PV to FV. The answer is -15,192.93 (a cash outflow). This means that after 10 years of borrowing $1,000 per year, you would have to repay $15,192.93 to satisfy the loan terms. Again, if you would prefer this to show as a positive number, you could change the sign of the annuity payment or put a negative sign in front of the function (i.e., =-FV(B3,B2,B1)).

Example 2.2 — Solving for the Payment Amount

We often need to solve for annuity payments. For example, you might want to know how much a mortgage or auto loan payment will be. Or, maybe you want to know how much you will need to save each year in order to reach a particular goal (saving for college or retirement perhaps). On the previous page, we looked at an example about saving for college. Let's look at that problem again, but this time we'll treat it as an annuity problem instead of a lump sum:

Suppose that you are planning to send your daughter to college in 18 years. Furthermore, assume that you have determined that you will need $100,000 at that time in order to pay for tuition, room and board, party supplies, etc. If you believe that you can earn an average annual rate of return of 8% per year, how much money would you need to invest at the end of each year to achieve your goal?

Recall that we previously determined that if you were to make a lump sum investment today, you would have to invest $25,024.90. That is quite a chunk of change. In this case, saving for college will be easier because we are going to spread the investment over 18 years, rather than all at once. (Note that, for now, we are assuming that the first investment will be made one year from now. In other words, it is a regular annuity.)

Open a new worksheet and enter the data as shown below:

In this problem we want to solve for an annual annuity payment, so we will use the PMT function. Select B5 and enter: =PMT(B3,B2,0,B1). Note that we entered 0 for the PV argument because the problem doesn't specify an initial investment. You will find that you need to invest $2,670.21 per year for the next 18 years to meet your goal of having $100,000.

Now, let's change the problem slightly by including a lump sum investment made today:

Suppose that you have just received a gift from one of your daughter's grandparents. They have given you $5,000 to be invested to help pay for her college tuition. How does this change the amount that you would have to invest each year?

Since you will now be investing $5,000 today (the PV), the amount that you need to save in future years will be reduced. To find out the new annual payment that is required, we need to modify the spreadsheet somewhat. First, select Row 1 and insert a new row. Now, in A1 type: Present Value and in B1 enter: 5,000.

Finally, we need to change the formula in B6 to: =PMT(B4,B3,-B1,B2). Notice that the PV argument has been changed from 0 to -B1. It has to be entered as a negative number because the $5,000 will be invested (a cash outflow). If you had put it in as a positive number, then you would get the wrong answer ($3,203.72). You should catch this error because the result is higher than if you didn't have the $5,000 to invest. Again, you always have to think about the direction of the cash flows when using these functions.

Example 2.3 — Solving for the Number of Periods

Solving for N answers the question, "How long will it take..." Let's look at an example:

Imagine that you have just retired, and that you have a nest egg of $1,000,000. This is the amount that you will be drawing down for the rest of your life. If you expect to earn 6% per year on average and withdraw $70,000 per year, how long will it take to burn through your nest egg (in other words, for how long can you afford to live)? Assume that your first withdrawal will occur one year from today.

In this problem, we know the present value ($1,000,000), the annual payment ($70,000), and the interest rate (6%). We want to know how long the money that you have now will last. In other words, we want to solve for the number of periods. Set up a worksheet to look like the one below:

Select B5 and enter: =NPER(B3,B2,-B1). You will see that you can make 33.40 withdrawals. Assuming that you can live for about a year on the last withdrawal, then you can afford to live for about another 34.40 years.

Now, let's change the problem slightly:

Suppose that you would like to leave an inheritance of at least $100,000 to your favorite charity. How does this affect the number of periods in which you can withdraw the $70,000 per year?

It should be obvious that the answer will be less than before because you aren't going to withdraw the entire $1,000,000. However, be aware that this is not the same as investing only $900,000 today because the $100,000 is a future value. Modify your worksheet so that it looks like the one below:

The formula in B6 needs to be changed to: =NPER(B4,B3,-B1,B2). Note that the future value argument (B2) should be entered as a positive number. In this case, saving $100,000 to give as an inheritance will reduce the amount of time that you can draw on your savings to 31.86 years.

Example 2.4 — Solving for the Interest Rate

Solving for the interest rate works just like solving for any of the other variables. As has been mentioned numerous times in this tutorial, be sure to pay attention to the signs of the numbers (or cell references) that you enter into the TVM functions. Any time you are solving for NPer, Rate, or PMT there is the potential for a wrong answer or error message if you don't get the signs right. Let's look at an example of solving for the interest rate:

Suppose that you are offered an investment that will cost $925 and will pay you interest of $80 per year for the next 20 years. Furthermore, at the end of the 20 years, the investment will pay $1,000. If you purchase this investment, what is your compound average annual rate of return?

Note that in this problem we have a present value ($925), a future value ($1,000), and an annuity payment ($80 per year). As mentioned above, you need to be especially careful to get the signs right. In this case, both the annuity payment and the future value will be cash inflows, so they should be entered as positive numbers. The present value is the cost of the investment, a cash outflow, so it should be entered as a negative number. If you were to make a mistake and, say, enter the payment as a negative number, then you will get the wrong answer. On the other hand, if you were to enter all three with the same sign, then you will get an error message.

Create a new worksheet like the one above. In B6 enter the formula: =RATE(B4,B3,-B1,B2). You will find that the investment will return an average of 8.81% per year. Again, notice that the PV (the amount that you will pay) is entered as a negative number while the PMT and FV are positive numbers because they represent cash inflows. This particular problem is an example of solving for the yield to maturity (YTM) of a bond.

Example 2.5 — Annuities Due

In the examples above, we assumed that the first payment would be made at the end of the year, which is typical. However, what if you plan to make (or receive) the first payment today? This changes the cash flow from from a regular annuity into an annuity due. Note that this only changes the timing of the cash flows; the functions and formulas that are used are the same.

By default, the time value of money functions assume that the cash flows occur at the end of the period. In this case, though, the payments occur at the beginning of the period. Therefore, we will need to use the Type argument in the functions, and make sure that it is set to 1. Note that nothing will change about how you enter the numbers. The functions will simply shift the cash flows for you. Obviously, you will get a different answer because the timing is different.

Let's do the college savings problem again, but this time assuming that you start investing immediately:

Suppose that you are planning to send your daughter to college in 18 years. Furthermore, assume that you have determined that you will need $100,000 at that time in order to pay for tuition, room and board, party supplies, etc. If you believe that you can earn an average annual rate of return of 8% per year, how much money would you need to invest at the beginning of each year (starting today) to achieve your goal?

This problem is identical to the one that we did earlier, but we are now treating this as an annuity due. So, you can use the same spreadsheet to solve the problem with only minor changes:

I have added a row for the Type argument, and set it (in B4) to 1. This isn't strictly necessary, but it will be helpful in a moment. In B6, enter the formula: =PMT(B3,B2,0,B1,B4). Previously, we left out the Type argument because Excel automatically sets it to 0 (end of period) if it is omitted. In this case, we had to include the argument.

You will find that, if you make the first investment today, you only need to invest $2,472.42. That is about $200 per year less than if you make the first payment a year from now because of the extra time for your investments to compound. Since we entered the Type argument in B4, you can change it to 0 and you will see that waiting until the end of the year to make the first investment means that you would have to invest $2,670.21 per year. The sooner you start investing, the less you have to invest to meet a goal.

Example 2.6 — Perpetuities

Occasionally, we have to deal with annuities that pay forever (at least theoretically) instead of for a finite period of time. This type of cash flow is known as a perpetuity (perpetual annuity, sometimes called an infinite annuity). The problem is that there is no way to specify an infinite number of periods for the NPer argument.

Calculating the present value of a perpetuity using a formula is easy enough: Just divide the payment per period by the interest rate per period. As an example, assume that the payment is $1,000 per year and the interest rate is 9% annually. Therefore, if that was a perpetuity, the present value would be:

$11,111.11 = 1,000 ÷ 0.09

Create a worksheet that looks like the one below:

If you can't remember the formula, you can "trick" the PV function into getting the correct answer. The trick involves the fact that the present value of a cash flow far enough into the future (way into the future) is going to be approximately $0. Therefore, beyond some future point in time the cash flows no longer add anything to the present value. So, if we specify a suitably large number of payments, we can get a very close approximation (in the limit it will be exact) to a perpetuity.

Let's try this with our perpetuity. Select row 3 and insert a new row. In A3 enter: Number of Years, and enter 500 into B3 (that will always be a large enough number of periods). You will get $11,111.11 as your answer.

Please note that there is no such thing as the future value of a perpetuity because the cash flows never end (period infinity never arrives). However, in the example spreadsheet Excel will calculate the future value as of period 500 (or whatever you enter into B3) because that is technically not an infinite amount of time in the future.

Please continue on to part III of this tutorial to learn about uneven cash flow streams, net present value, internal rate of return, and modified internal rate of return.