In this video we discuss how to calculate the present value of an ordinary annuity by hand in excel. We go through how to enter the formula step by step into a cell in excel
Transcript/notes
Here is the formula to calculate the present value of an ordinary annuity, 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 expects to receive an $8000 annuity for the next 3 years. Interest is 8% annually and the payments are received at the end of the year, so this is an ordinary annuity. What is the present value of the annuity?
We are going to put the answer in cell B8, so we left click on cell B8, so it is highlighted. Next, we type in an equals sign, then, following the formula, we left click on the payment amount, cell B2. From here we type in a multiplication sign, which is shift 8 on the keyboard. Next, we type in 2 open parenthesis, then we type in a 1.
From here, we type in a minus sign, then another open parenthesis. Next, we type in a 1, then we type in a division sign, which is a forward slash in excel. From here we type in 2 open parentheses, then we type in another 1.
Next, we type in an addition sign followed by another open parenthesis. Now we need to left click on the rate, cell B3, then type in another division sign. From here, we need to left click on the number of compounding periods, cell B4, then we type in 2 closed parentheses. Next, we type in a to a power symbol, or carrot sign, which is shift 6 on the keyboard, and then we type in an open parenthesis.
Now we need to left click on the number of compounding periods, cell B4, then type in a multiplication sign. Next, we left click on the time, cell B5, and then we type in 4 closed parentheses. From here, we type in a division sign, followed by an open parenthesis. Then we left click on the rate, cell B3, and then type in another division sign. Next, we left click on the number of compounding periods, cell B4, and then we type in a closed parenthesis to close off the function.
And finally, we can hit the enter key and we have our answer of $20,616.78 rounded off.
Here is another example, where n, the number of compounding periods is not 1, here it is 4, I will hit the enter key and we get $10,312.12 as the present value of the annuity.
Chapters/Timestamps
0:00 Written formula for the present value of an ordinary annuity
0:10 Example set up
0:23 Start of excel example formula
2:06 Answer to example problem
2:17 Another example problem