How to Calculate Deferred Annuity in Excel (2 Quick Methods)

Get FREE Advanced Excel Exercises with Solutions!

While working in Microsoft Excel sometimes we need to calculate different types of annuities like Immediate annuity, deferred annuity, lifetime annuity, fixed-period annuity, etc. Annuities differ from one another according to payment terms. Today in this article, I am sharing with you how to calculate deferred annuity in Excel.


Introduction of Deferred Annuity

In order to understand deferred annuity, you have to know about annuities first. An Annuity is an agreement between you and your insurer where you pay a lump-sum amount or series of money to get a return immediately or after a long period of time. If you want your return immediately then it’s known as an immediate annuity. On the other hand, If you choose to get the return after a long period it will be called a deferred annuity. There are various advantages with deferred annuities like you will get a good amount of profit if you choose a longer period of time to collect your return. Mostly, retirement plans are covered with a deferred annuity.


How to Calculate Deferred Annuity in Excel: 2 Simple Methods

In the following, I have explained 2 simple methods to calculate deferred annuity in Excel.


1. Calculate Deferred Annuity If Payment Is Ordinary Due

While calculating deferred annuity sometimes you will find the total ordinary amount. In this method, I am explaining how to calculate deferred annuity when you have ordinary due payments on your hands.

Suppose you have the “Total Amount”, “No. of payment in Year”, “Rate of Interest”, “No. of Deferred period”, and “No. of Period” in your hand. Now we will determine the deferred annuity value using a simple formula.

Calculate Deferred Annuity If Payment Is Ordinary Due

Steps:

  • Starting with determining the deferred amount choose a cell (C11) to apply the formula.
  • Write down the following formula-
=C5*(1-(1+C7)^(-C9))/(((1+C7)^C8)*C7)

Where,

  • The Deferred Annuity = P-Ordinary * [1 – (1 + r)-n] / [(1 + r)t * r]

Calculate Deferred Annuity If Payment Is Ordinary Due

  • After that press Enter and here we have our deferred amount in our hands just by applying a simple formula.

Read More: How to Do Ordinary Annuity in Excel


2. Calculate Deferred Annuity If Payment Is Annuity Due

Well, you can also determine the deferred value if you have the total due amount.

Just like the previous method, we have the total due amount in our dataset. Now we will calculate the deferred annuity or grand with a formula.

Steps:

  • Above all, choose a cell (C11) to write the formula down-
=C5*(1-(1+C7)^(-C9))/(((1+C7)^(C8-1)*C7))

Where,

  • The Deferred Annuity = P-Due * [1 – (1 + r)-n] / [(1 + r)t-1 * r]

Calculate Deferred Annuity in Excel If Payment Is Annuity Due

  • Hit the Enter button to get the output.
  • Finally, we have successfully calculated deferred annuity in Excel.

Calculate Deferred Annuity in Excel If Payment Is Annuity Due

Read More: How to Calculate Annuity Due in Excel


Things to Remember

  • While applying the formula, collect all the data and put closing brackets at the end of the arguments.

Download Practice Workbook

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


Conclusion

In this article, I have tried to cover all the steps to calculate deferred annuity in Excel. Take a tour of the practice workbook and download the file to practice by yourself. I hope you find it helpful. Please inform us in the comment section about your experience. Stay tuned and keep learning.


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.
Wasim Akram
Wasim Akram

Wasim Akram holds a BSc degree in Industrial and Production Engineering from Ahsanullah University of Science and Technology. Over the past 2 years, he has been actively contributing to the ExcelDemy project, where he has authored more than 150 articles. Now, he is working as an Excel VBA and Content Developer. He likes learning new things about Microsoft Office, especially Excel VBA, Power Query, Data Analysis, and Excel Statistics. He is also very interested in machine learning and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo