Calculating PV of Annuity in Excel

Claire Boyte-White is the lead writer for NapkinFinance.com, co-author of I Am Net Worthy, and an Investopedia contributor. Claire's expertise lies in corporate finance & accounting, mutual funds, retirement planning, and technical analysis.

Updated September 30, 2021 Reviewed by Reviewed by Marguerita Cheng

Marguerita is a Certified Financial Planner (CFP), Chartered Retirement Planning Counselor (CRPC), Retirement Income Certified Professional (RICP), and a Chartered Socially Responsible Investing Counselor (CSRIC). She has been working in the financial planning industry for over 20 years and spends her days helping her clients gain clarity, confidence, and control over their financial lives.

Fact checked by Fact checked by Suzanne Kvilhaug

Suzanne is a content marketer, writer, and fact-checker. She holds a Bachelor of Science in Finance degree from Bridgewater State University and helps develop content strategies for financial brands.

Calculating the present value of an annuity using Microsoft Excel is a fairly straightforward exercise, as long as you know a given annuity's interest rate, payment amount, and duration. But it's important to stipulate that calculating this value is only feasible when dealing with fixed annuities.

This same calculation cannot be made with variable annuities, due to the simple fact that their rates of return fluctuate, usually in tandem with a stock market index or a money market index. This makes variable annuities more difficult to value accurately, and leaves investors in the untenable position of having to blindly guess at future rates.

Pricing a Fixed Annuity in Excel

The price of a fixed annuity is the present value of all future cash flows. In other words, an investor would have to know the amount of money they must pay today in order to receive the stated rate of return for the duration of the annuity.

For example, if an individual wished to receive $1,000 per month for the next 15 years, and the stated annuity rate was 4%, they can use Excel to determine the cost of setting up this offering.

This calculation does not account for the income taxes due on the annuity payouts.

(If the chart is hard to read, please right-click and choose "view image.")

Compare Accounts Advertiser Disclosure

The offers that appear in this table are from partnerships from which Investopedia receives compensation. This compensation may impact how and where listings appear. Investopedia does not include all offers available in the marketplace.

Description Related Articles

adult woman sitting at a table and looking at a computer in a home setting

The Annuity Formula for the Present and Future Value of Annuities

Present Value Interest Factor of Annuity (PVIFA): A factor that can be used to calculate the present value of a series of annuities.

Present Value Interest Factor of Annuity (PVIFA) Formula, Tables

Present Value of an Annuity: The current value of future payments from an annuity.

Present Value of an Annuity: Meaning, Formula, and Example

Charitable Gift Annuity: Meaning, Regulations, FAQs

Older couple sitting on their couch and looking happily at something on their laptop screen

Who Benefits From Retirement Annuities?

A woman stands at a kitchen counter looking at her smartphone

What Is an Annuity? Definition, Types, and Tax Treatment Partner Links Related Terms

The present value interest factor of an annuity is calculated to compare the real value of a lump sum payment today and the same amount of money paid over time.

The present value of an annuity is the current value of future payments from that annuity, given a specified rate of return or discount rate.

A charitable gift annuity is an arrangement for a series of income payments for life, to be paid to an individual in return for a donation of assets.

An annuity is a financial product that pays out a fixed and reliable stream of income to an individual, which is typically of primary importance to retirees.

Period certain is a life annuity option that allows the customer to choose when and how long to receive payments, which beneficiaries can later receive.

Assumed interest rate (AIR) is defined as the rate of interest or growth rate selected by an insurance company.

Investopedia is part of the Dotdash Meredith publishing family.

We Care About Your Privacy

We and our 100 partners store and/or access information on a device, such as unique IDs in cookies to process personal data. You may accept or manage your choices by clicking below, including your right to object where legitimate interest is used, or at any time in the privacy policy page. These choices will be signaled to our partners and will not affect browsing data.

We and our partners process data to provide:

Store and/or access information on a device. Use limited data to select advertising. Create profiles for personalised advertising. Use profiles to select personalised advertising. Create profiles to personalise content. Use profiles to select personalised content. Measure advertising performance. Measure content performance. Understand audiences through statistics or combinations of data from different sources. Develop and improve services. Use limited data to select content. List of Partners (vendors)