Mashhura Jahan
If you want to calculate Annuity due, Excel can come really handy. Excel has some functions that can help to calculate annuity due. The main objective of this article is to explain how to calculate annuity due in Excel.
Download Practice Workbook
What Is Annuity Due?
A recurring payment that is made at the start of each period is known as an annuity due. It provides the holder with a stream of regular interest payments.
The current price of a series of cash flows from an annuity due that starts right away is the present value of annuity due. At the start of each period, the annuity payments are distributed.
The formula for calculating the present value of annuity due is,
Present Value of Annuity Due = P+P[{1-(1+r)-(n-1)}/r]
Where,
P = Payment Per Period
r = Rate of Interest
n = Periods
The future value of annuity is a tool to aid in analyzing a financial investment’s potential for cash flow. The main purpose of the future value of an annuity due is to determine how much a particular sequence of annuity payments would be worth at a specific future date when combined with a specific interest rate.
The formula for the future value of annuity due is,
Future Value of Annuity Due = P*{(1+r)n-1}*(1+r)/r
Where,
P = Payment Per Period
r = Rate of Interest
n = Periods
3 Simple Ways to Calculate Annuity Due in Excel
To explain this article I have taken the following dataset that contains the Amount Details of Payment Per Period, Rate of Interest, and Periods. I will calculate the annuity due for this dataset.
1. Using Generic Formula to Calculate Annuity Due in Excel
In this first method, I will use the generic formula to calculate annuity due in Excel. I will calculate both the present value and future value of annuity due.
1.1. Calculating Present Value of Annuity Due
In this example, I will use the generic formula to calculate the present value of annuity due. Suppose, your Payment Per Period is $4000, the Rate of Interest is 5% and there is a total of 5 Periods. Now, I will show you how to calculate the present value of annuity due in Excel using these data.
Let’s see the steps.
Steps:
- Firstly, select the cell where you want your present value of annuity due. Here, I selected cell C9.
- Secondly, in cell C9 write the following formula.
=C5+C5*((1-(1+C6)^-(C7-1))/C6)
Formula Breakdown
- Here, the formula will raise (1+C6) to the power of -(C7-1).
- Then, subtract the result from 1.
- Next, It will divide the result by C6.
- After that, it will multiply it by C5.
- Finally, it will sum the previously gained result with C5 and return the present value of annuity due.
- Finally, press ENTER to get the result.
Read More: How to Calculate Annuity in Excel (5 Practical Examples)
1.2. Calculating Future Value of Annuity Due
In this example, I will use the generic formula to calculate the future value of annuity due. Suppose, your Payment Per Period is $4000, the Rate of Interest is 5% and there is a total of 5 Periods. Now, I will show you how to calculate the future value of annuity due in Excel using these data.
Let’s see the steps.
Steps:
- Firstly, select the cell where you want to calculate your future value of annuity due. Here, I selected cell C9.
- Secondly, in cell C9 write the following formula.
=C5*((1+C6)^C7-1)*(1+C6)/C6
Formula Breakdown
- Here, the formula will raise (1+C6) to the power of C7. And then subtract 1 from it.
- Next, It will multiply the result by C5 and (1+C6).
- After that, It will divide the previously gained result by C6.
- Finally, press ENTER to get the result.
Read More: How to Calculate Annuity Factor in Excel (2 Ways)
2. Use of PV Function to Calculate Present Value of Annuity Due in Excel
In this method, I will explain how to calculate the present value of annuity due in Excel with help of the PV function.
For example, your Payment Per Period is $4000, the Rate of Interest is 5% and there is a total of 5 Periods. Now, I will calculate the present value of annuity and then the present value of annuity due with this dataset.
Let’s see the steps.
Steps:
- Firstly, select the cell where you want your present value of annuity. Here, I selected cell C9.
- Secondly, in cell C9 write the following Formula.
=PV(C6,C7,-C5,0,0)
Here, in the PV function, I selected C6 as rate, C7 as nper, -C5 as pmt, 0 as FV, and 0 for type. Now, the formula will return us the value of the present value of annuity.
- Finally, press ENTER to get the present value of annuity.
Now, I will calculate the present value of annuity due.
- Firstly, select the cell where you want your present value of annuity due. Here, I selected cell C10.
- Secondly, in cell C10 write the following Formula.
=C9*(1+C6)
Here, the formula will sum C6 with 1 and then, multiply the result by C9. This will return you the present value of annuity due.
- Finally, press ENTER to get the present value of annuity due.
Read More: How to Do Ordinary Annuity in Excel (2 Methods)
3. Using FV Function to Calculate Future Value of Annuity Due in Excel
In this method, I will explain how to calculate the future value of annuity due in Excel with help of the FV function.
For example, your Payment Per Period is $4000, the Rate of Interest is 5% and there is a total of 5 Periods. Now, I will calculate the future value of annuity and then the future value of annuity due with this dataset.
Let’s see the steps.
Steps:
- Firstly, select the cell where you want your future value of annuity. Here, I selected cell C9.
- Secondly, in cell C9 write the following Formula.
=FV(C6,C7,-C5,0,0)
Here, in the FV function, I selected C6 as rate, C7 as nper, -C5 as pmt, 0 as PV, and 0 for type. Now, the formula will return us the value of the future value of annuity.
- Finally, press ENTER to get the future value of annuity.
Now, I will calculate the future value of annuity due.
- Firstly, select the cell where you want your future value of annuity due. Here, I selected cell C10.
- Secondly, in cell C10 write the following Formula.
=C9*(1+C6)
Here, the formula will sum C6 with 1 and then, multiply the result by C9. This will return you the future value of an annuity due.
- Finally, press ENTER to get the future value of an annuity due.
Read More: How to Calculate Future Value of Growing Annuity in Excel
Practice Section
Here, I have provided a practice sheet for you to practice how to calculate annuity due in Excel.
Conclusion
In this article, I tried to cover how to calculate annuity due in Excel. Here, I explained 3 different methods. If you find it helpful, you can check ExcelDemy for more articles like this. More importantly, I suggest you practice by yourself to get perfection. And lastly, if you have any questions or any suggestions don’t forget to let me know in the comment section below.
