• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer
Finance Train

Finance Train

High Quality tutorials for finance, risk, data science

  • Home
  • Data Science
  • CFA® Exam
  • PRM Exam
  • Tutorials
  • Careers
  • Products
  • Login

Using Excel to Calculate Annuity

Excel Modelling, Financial Mathematics

An annuity refers to a series of equal cash flows that occur periodically such as monthly or annually. For example, an investment that gives you fixed monthly payments for a specified period. There are two types of annuities, namely, regular annuities and annuities due. In a regular annuity, the first cash flow occurs at the end of the first period, and in an annuity due, the first cash flow occurs at the beginning (at time 0).

We can use time value of money functions in Excel to calculate both regular annuity and annuity due.

The common variables in these formulas are:

  • rate is the periodic interest rate
  • nper is the number of payments
  • pv is the initial principal or the present value
  • fv refers to future value.
  • type is whether the annuity is a regular or an annuity due. Use 0 for regular annuities, and 1 for annuity due. By default type is 0.
  • Any variable given in squared brackets is optional.

There are five key functions to calculate different aspects of an annuity:

=PMT( rate, nper, pv, [fv], [type]) This function calculates the periodic payments in an annuity.
=PV( rate, nper, pmt, [fv], [type]) This function calculates the present value of an annuity, once we have the periodic payments.
=FV( rate, nper, pmt, [pv], [type]) This function calculates the present value of an annuity, once we have the periodic payments.
=NPER( rate, pmt, pv, [fv], [type]) This function calculates the number of payments in an annuity.
=RATE( nper, pmt, pv, [fv], [type],[guess]) This function calculates the interest rate earned in the annuity. Guess is your guess for what the rate will. By default its value is 0.10.

Example 1

Let’s take an example to understand this. Let’s say that an investment pays you $100 per month for the next 10 years. If this annuity is actually paying 8% interest, how much principal do you have to pay for this.

Rate = 8% per annum or 8%/12 per month

PMT = $100

Nper = 10*12 = 120 months

The principal can be calculated using the PV formula:

=PV(8%/12,120,100)

=$8,242

So, if you make an investment of $8,242 in this annuity, it will pay you $100 per month for the next 10 years.

Note that we did not fill the valuye for [type] in the formula. This assumed that it’s a regular annuity. If it was an annuity due, the calculation would be:

=PV(8%/12,120,100,0,0)

=$8,297

Example 2

Let’s take another example. Let’s say you require $100,000 after five years for your daughter’s marriage. You know that you can earn a rate of 9% on your investments. You much money should you save and invest every month, so that at the end of 5 years it becomes $100,000.

Rate = 9% per annum or 9%/12 per month

Nper = 5*12 = 60 months

Future Value (FV) = $100,000

We can use the PMT function to calculate the monthly payments/investments.

=PMT(9%/12, 60, 0, 100,000)

=$1,325.84

You should invest $1,325.84 every month for the next 5 years at 9% interest rate to have $100,000 at the end of the period.

Join Our Facebook Group - Finance, Risk and Data Science

Posts You May Like

How to Improve your Financial Health

CFA® Exam Overview and Guidelines (Updated for 2021)

Changing Themes (Look and Feel) in ggplot2 in R

Coordinates in ggplot2 in R

Facets for ggplot2 Charts in R (Faceting Layer)

Reader Interactions

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Primary Sidebar

Latest Tutorials

    • Data Visualization with R
    • Derivatives with R
    • Machine Learning in Finance Using Python
    • Credit Risk Modelling in R
    • Quantitative Trading Strategies in R
    • Financial Time Series Analysis in R
    • VaR Mapping
    • Option Valuation
    • Financial Reporting Standards
    • Fraud
Facebook Group

Membership

Unlock full access to Finance Train and see the entire library of member-only content and resources.

Subscribe

Footer

Recent Posts

  • How to Improve your Financial Health
  • CFA® Exam Overview and Guidelines (Updated for 2021)
  • Changing Themes (Look and Feel) in ggplot2 in R
  • Coordinates in ggplot2 in R
  • Facets for ggplot2 Charts in R (Faceting Layer)

Products

  • Level I Authority for CFA® Exam
  • CFA Level I Practice Questions
  • CFA Level I Mock Exam
  • Level II Question Bank for CFA® Exam
  • PRM Exam 1 Practice Question Bank
  • All Products

Quick Links

  • Privacy Policy
  • Contact Us

CFA Institute does not endorse, promote or warrant the accuracy or quality of Finance Train. CFA® and Chartered Financial Analyst® are registered trademarks owned by CFA Institute.

Copyright © 2021 Finance Train. All rights reserved.

  • About Us
  • Privacy Policy
  • Contact Us