In this video we discuss how to calculate the future value of an annuity due by hand in excel. We go through a few examples, including when the payment or deposit periods do not match the compounding periods

Transcript/notes (partial)
Here is the formula to calculate the future value of an annuity due with all of the variables listed. We are going to go through how to input this formula into excel.

As an example, let’s say that someone deposits $3000 at the end of each year for 4 years, into an investment earning 7% compounded yearly. What is the future value of the annuity?

And one important note, to use this formula, the interest compounding periods must match the payment periods, for instance, yearly, semi-annually or quarterly. This is extremely important. So, the variable n, must match the number of payment periods per year.

I have listed all of the variables with their amounts, as you see on the screen, so we can reference them when we enter the formula into a cell.

We are going to put the answer in cell B9, so we left click on cell B9, so it is highlighted. Next, we type in an equals sign, then, following the formula, we left click on the payment or deposit amount, cell B2. From here we type in a multiplication sign, which is shift 8 on the keyboard. Next, we type in 4 open parenthesis, then we type in a 1. From here, we type in an addition sign, then another open parenthesis. Next, we need to left click on the interest rate amount, cell B3, then type in a division sign, which is a forward slash on the keyboard.

Now we need to left click on the number of deposit periods per year, cell B5. We always use the deposit periods, if the compounding periods do not match the deposit periods, we have to adjust the interest rate, and we will go through that in the next example.

Next, we type in 2 closed parenthesis, to close off the r over n portion and the 1 plus r over n portion of the formula. From here, we type in the to a power symbol, called a carrot sign, which is shift 6 on the keyboard. Next we left click on the number of deposit or payment periods, cell B5, then we type in a multiplication sign, and then we left click on the number of years, cell B6. Now we type in 2 closed parentheses, to close off the n times t portion and the 1 plus r over n raised to the n times t portion of the formula.

Next, we type in a minus sign, then we type in a 1, followed by a closed parenthesis, which closes off the top part of the fraction in the formula. From here, we type in a division sign, a forward slash, then type in an open parenthesis. Next we left click on the rate, cell B3, then type in a division sign. Now, we left click on the number of deposit periods, cell B5. We use the number of deposit periods because, as you will see in examples where the deposit periods and compounding periods do not match, we need to get them to match.

Then we type in 2 closed parentheses to close off the bottom part of the fraction. Next, we type in a multiplication sign, then another open parentheses. From here, we type in a 1 followed by an addition sign. Next we left click on the rate, cell B3, then we type in a division sign, then we left click on the deposit periods, cell B5.

From here, we hit the enter key, and we have our answer of $14,252.22 rounded off, which is the future value of the annuity.

Now for an example where the deposit or payment periods do not match compounding periods. Let’s say this time, that someone deposits $3000 at the end of each year for 4 years, into an investment earning 7% compounded quarterly. What is the future value of the annuity?

The only thing that has changed from the previous example problem is that there are 4 compounding periods per year. So, I am going to list the variables with the amounts, with that 1 change.

As I stated earlier, to use the formula, the interest compounding periods must match the payment or deposit periods.

To solve this problem, we need to convert the interest rate to the effective interest rate. So, I am going to add that variable to the list. Here is the formula to calculate the effective interest rate with all of the variables listed. We are going to enter this formula into cell B19, so, we left click on cell B19. Next, we type in an equals sign followed by 2 open parentheses.

Chapters/Timestamps
0:00 Formula for future value of an annuity due
0:10 Example 1 set up
0:44 Begin example 1 how to calculate future value of an annuity due in excel by hand
3:13 Final excel formula for example 1
3:26 Begin example 2, when payment periods and compounding periods do not match
4:00 How to convert the rate to the effective rate
5:16 Final excel formula for example 2
5:40 Begin example 3
6:19 How to convert the effective rate to the nominal rate in excel
7:38 Final excel formula for example 3