Md. Abdur Rahim Rasel
An annuity that pays out at the end or beginning of each period is referred to as an ordinary annuity. We can easily calculate the Ordinary Annuity by using the Microsoft Excel financial formulas. This is an easy task. Today, in this article, we’ll learn two quick and suitable ways to do ordinary annuity in Excel effectively with appropriate illustrations.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Introduction to Ordinary Annuity
The sequence of equal future payments that are discounted to the present day is referred to as an “annuity” or “present value of the annuity.” However, there are two distinct formulations since the payment might be paid either at the start or the end of each quarter. The present value of an annuity due is the term used when the cash flow is expected to be paid all at once. The formula may obtain based on the periodic payment schedule, interest rate, duration in years, and frequency of occurrence in a year. It denotes mathematically by the following:
Where,
- PVA = Present Value of an Ordinary Annuity.
- P = The Periodic Payment.
- r = Rate of Interest.
- t = Number of total Years.
- n = Frequency of Occurrence in a year.
The present value of an ordinary annuity is what is used when the cash flow is expected to be paid at the end of each month. The calculation is somewhat different and represents as,
2 Suitable Ways to Do Ordinary Annuity in Excel
Let’s assume, we have an Excel large worksheet that contains the information about the Ordinary Annuity calculation. From our dataset, we will calculate the Ordinary Annuity by using the Mathematical formula and the PV financial formula in Excel. Here’s an overview of the dataset for today’s task.
1. Use Mathematical Formula to Do Ordinary Annuity in Excel
In this method, we will use the Mathematical formula to calculate the Ordinary Annuity. You can calculate the Ordinary Annuity using the Mathematical formula in two ways. One is at the end of each month and the other is at the beginning of the month. From our dataset, we will calculate the Ordinary Annuity for both cases. Let’s follow the instructions below to learn!
Step 1:
- First of all, we will calculate the present value of the Annuity Due.
- To do that, select cell D8.
- Hence, write down the Mathematical formula in that cell. The Mathematical formula is,
=D7*(1-(1+D4/D6)^-(D5*D6))*((1+D4/D6)/(D4/D6))- Where D7 is the Original Balance, D4 is the rate of Interest, D5 is the number of years, and D6 is the number of payments per year.
- Hence, simply press ENTER on your keyboard.
- As a result, you will get the Annuity at the end of the month, and the value is $2,128,001.21.
Step 2:
- Now, we will calculate the Annuity at the beginning of the month.
- To do that, select cell D9, and write down the Mathematical formula in that cell. The Mathematical formula is,
=D7*(1-(1+D4/D6)^-(D5*D6))/(D4/D6)- Where D7 is the Original Balance, D4 is the rate of Interest, D5 is the number of years, and D6 is the number of payments per year.
- Hence, simply press ENTER on your keyboard.
- As a result, you will get the Annuity at the beginning of the month, and the value is $2,101,729.59 which you can see in the screenshot below.
Read More: How to Calculate Annuity Payments in Excel (4 Suitable Examples)
2. Apply PV Function to Do Ordinary Annuity in Excel
Now, we will apply the PV financial function to calculate the Ordinary Annuity. From our dataset, we can easily calculate the Ordinary Annuity using the PV financial function. This is an easy and time-saving task. Let’s follow the instructions below to calculate the Ordinary Annuity!
Steps:
- First of all, we will calculate the present value of the Annuity Due.
- To do that, select cell D8.
- After that, write down the PV financial function in that cell. The PV financial function is,
=PV(D4/D6,D5*D6,D7,0,0)- Where D4 is the Annual Interest Rate, D6 is the number of payments per year, D5 is the number of years, D7 is the original price of the Annuity, first 0 is the [fv], and second 0 is the end of the period of the PV function.
- Hence, simply press ENTER on your keyboard.
- As a result, you will get the return of the PV function, and the return is $2,353,268.45.
Read More: How to Apply Present Value of Annuity Formula in Excel
Things to Remember
➜ While a value can not found in the referenced cell, the #N/A! error happens in Excel.
➜ #DIV/0! error happens when a value is divided by zero(0) or the cell reference is blank.
Conclusion
I hope all of the suitable methods mentioned above to calculate the ordinary annuity will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.
