Calculating The Value Of An Annuity In Excel

Excel function tutorials

If you want to learn Excel, keep reading or Get 10 days of free unlimited access to for professional help and Excel Tutorials

I’ve been discussing annuities quite a bit in recent days so i thought it would be interesting to give a shot at calculating the value of an annuity in excel. This is obviously not a perfect method and I’ll try to give you an idea of things that could be done to improve on the calcs but it does give you a good idea.

This is a very simple annuity where someone pays an amount to start receiving an annual amount at a given age until he or she dies. So basically I want to determine the cost. I need to know what the annuity would be paying at that point every year, if the amount will be adjusted for inflation, etc.

I first looked for data on life expectancy which ended up being available per country (it does of course depend on being a man or woman). Here is a sample:

Then I entered a place for assumptions:

And finally a place where I did all of my calcs:

I used two excel functions that I’ve used here before, vlookup and PV. You can see the whole details in my spreadsheet. Here is a snapshot of my answer for my initial assumptions:

I also did a tab where I prove my calcs. To be fair, there are several things that could be improved:

-life expectancy will be higher by the time a current 32 year-old dies so that number should be boosted
-other factors would help to determine if the person will live a long life (general health, etc)
-inflation and market returns could certainly be changed

You can take a look at the spreadsheet here


Look Good at Work and Become Indispensable Become an Excel Pro and Impress Your Boss


58 Feedbacks on "Calculating The Value Of An Annuity In Excel"

​buzz actu

Having read this I thought it was extremely enlightening. I appreciate
you spending some time and effort to put this short article together.
I once again find myself spending a significant amount of time both reading and commenting.

But so what, it was still worth it!

Cheap Iphone 6 Cases

If you are going for best contents like me, just
pay a visit this site daily for the reason that it provides
feature contents, thanks


Asking questions are really pleasant thing if you are not understanding something entirely, except this paragraph provides nice understanding

orthodontist Hialeah

You really make it seem so easy with your presentation but I find
this matter to be really something which I think I would
never understand. It seems too complicated and extremely broad for me.
I’m looking forward for your next post, I’ll try
to get the hang of it!


It’s hard to come by educated people on this subject, however, you
sound like you know what you’re talking about! Thanks

download film bokep

It’s a pity you don’t have a donate button! I’d definitely donate to
this fantastic blog! I suppose for now i’ll settle for bookmarking and adding your RSS feed
to my Google account. I look forward to fresh updates and will share this website with my Facebook group.
Talk soon!

Infographiste Marseille

Hello, i think that i noticed you visited my blog thus i came to return the favor?.I’m attempting to to find things to improve my site!I guess its ok to use some
of your concepts!!

amazon fire tv Hd

If you desire to take much from this article then you have to apply such techniques to your won webpage.


Please Leave a Comment!

Please note: Comments may be moderated. It may take a while for them to show on the page.