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

Table of Contents hide

Download Practice Workbook

Introduction of Deferred Annuity

2 Simple Methods to Calculate Deferred Annuity in Excel

1. Calculate Deferred Annuity If Payment Is Ordinary Due

2. Calculate Deferred Annuity If Payment Is Annuity Due

Things to Remember

Conclusion

Download Practice Workbook

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

Calculate Deferred Annuity.xlsx

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.

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]

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

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]

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

Things to Remember

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

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. We, the Exceldemy team, are always responsive to your queries. Stay tuned and keep learning.