How to Do Ordinary Annuity in Excel (2 Methods)

Get FREE Advanced Excel Exercises with Solutions!

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.


Ordinary Annuity: Overview

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:

do ordinary annuity in excel

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,


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.

do ordinary annuity in excel


1. Using 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.

Use Mathematical Formula to Do Ordinary Annuity in Excel

  • 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.

Use Mathematical Formula to Do Ordinary Annuity in Excel

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.

Use Mathematical Formula to Do Ordinary Annuity in Excel

Read More: How to Calculate Annuity Payments in Excel


2. Applying the Excel PV Function to Do Ordinary Annuity

Now, we will apply the PV 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.

Apply PV Function to Do Ordinary Annuity in Excel

  • 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.

Apply PV Function to Do Ordinary Annuity in Excel

Read More: How to Calculate Deferred Annuity 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.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


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.


Related Articles


<< Go Back to Excel Annuity Formula | Excel Formulas for Finance | Excel for Finance | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Abdur Rahim Rasel
Md. Abdur Rahim Rasel

MD. ABDUR RAHIM is a marine engineer proficient in Excel and passionate about programming with VBA. He views programming as an efficient means to save time while managing data, handling files, and engaging with the internet. His interests extend to Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning, reflecting his versatile skill set. He earned a B.Sc in Naval Architecture & Marine Engineering from BUET, and now he has become a content developer, creating technical content... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo