Calculating The Value Of An Annuity In Excel

Excel function 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


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

cari kost di brebes

Helpful info. Fortunate me I found your site by chance, and I’m shocked why
this accident didn’t took place earlier! I bookmarked it.


Excellent site you have here but I was curious about if you knew
of any user discussion forums that cover the same topics talked
about in this article? I’d really like to be
a part of group where I can get feedback from other knowledgeable individuals that share the same interest.
If you have any suggestions, please let me know.
Thanks a lot!

Top 27 Annuity Articles from Non-Biased Sources |

[…] Calculating the Value of An Annuity in Excel @ Experiments in Finance (US) […]

Cosmetic Dentist Tulsa OK

It’s impressive that you are getting ideas from this post as well as
from our argument made at this time.

insited design

This design is steller! You definitely know how to keep a reader entertained.
Between your wit and your videos, I was almost moved to start my own blog (well, almost…HaHa!) Wonderful job.
I really enjoyed what you had to say, and more than that, how
you presented it. Too cool!