While working in Microsoft Excel sometimes we need to calculate different types of annuities like Immediate annuity, deferred annuity, a 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.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
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.
2 Simple Methods to Calculate Deferred Annuity in Excel
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.
- Starting with determining the deferred amount choose a cell (C11) to apply the formula.
- Write down the following formula-
- The Deferred Annuity = P-Ordinary * [1 – (1 + r)-n] / [(1 + r)t * r]
- 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 Methods)
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.
- Above all, choose a cell (C11) to write the formula down-
- The Deferred Annuity = P-Due * [1 – (1 + r)-n] / [(1 + r)t-1 * r]
- Hit the Enter button to get the output.
- Finally, we have successfully calculated deferred annuity in excel.
Read More: How to Calculate Annuity Due in Excel (3 Suitable Ways)
Things to Remember
- While applying the formula, collect all the data and put closing brackets at the end of the arguments.
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. We, the Exceldemy team, are always responsive to your queries. Stay tuned and keep learning.
- How to Calculate Annuity Factor in Excel (2 Ways)
- Calculate Growing Annuity in Excel (2 Easy Ways)
- How to Calculate Equivalent Annual Annuity in Excel (2 Examples)
- Calculate Annuity Payments in Excel (4 Suitable Examples)
- How to Apply Present Value of Annuity Formula in Excel
- Apply Future Value of an Annuity Formula in Excel
- How to Find Interest Rate in Future Value Annuity (2 Examples)