Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • Perform case-sensitive Lookup in Excel
  • How to use Excel OFFSET function
  • Offset in Excel
  • How to get address of first cell in range in Excel
  • Multi-criteria lookup and transpose in Excel

Data Analysis Examples

  • Remove Duplicates Example in Excel
  • How To Filter Data in Excel
  • How to Create Thermometer Chart in Excel
  • Excel Line Chart
  • Move chart to a separate worksheet in Excel

Data Validation Examples

  • Excel Data validation no punctuation
  • Excel Data validation number multiple 100
  • Excel Data validation require unique number
  • Excel Data validation must contain specific text
  • Excel Data validation whole percentage only

How to calculate present value of annuity in excel

by

To get the present value of an annuity, you can use the PV function.

Formula

=PV(rate,periods,payment,0,0)

In the example shown, the formula in C9 is:

=PV(C5,C6,C4,0,0)

Explanation

An annuity is a series of equal cash flows, spaced equally in time. In this example, an annuity pays 10,000 per year for the next 25 years, with an interest rate (discount rate) of 7%. To calculate present value, the PV function is configured as follows:

Worked Example:   PMT, RATE, NPER, PV and FV Financial Functions in Excel
  • nper – the value from cell C8, 25.
  • pmt – the value from cell C6, 100000.
  • rate – the value from cell C7, 7%.
  • fv – 0.
  • type – 0, payment at end of period (regular annuity).
Worked Example:   PV function: Description, Usage, Syntax, Examples and Explanation

With this information, the present value of the annuity is $116,535.83. Note payment is entered as a negative number, so the result is positive.

Annuity due

With an annuity due, payments are made at the beginning of the period, instead of the end. To calculate present value for an annuity due, use 1 for the type argument. In the example shown, the formula in F9 is:

=PV(F7,F8,-F6,0,1)

Note the inputs (which come from column F) are the same as the original formula. The only difference is type = 1.

Worked Example:   Bond valuation example in Excel

Post navigation

Previous Post:

Example of payment for annuity in Excel

Next Post:

Calculate loan interest in given year in Excel

Leave a Reply Cancel reply

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

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others
  • How to calculate Number of Instances in Excel
  • How to extract text between parentheses in Excel
  • T function: Description, Usage, Syntax, Examples and Explanation
  • How to convert numbers to text using TEXT function in Excel
  • Get first name from full name — Manipulating NAMES in Excel
  • Convert Excel time to Unix time in Excel
  • Get day from date in Excel
  • Add workdays no weekends in Excel
  • Pad week numbers with zeros in Excel
  • Add business days to date in Excel
  • ODDLYIELD function: Description, Usage, Syntax, Examples and Explanation
  • RATE function: Description, Usage, Syntax, Examples and Explanation
  • COUPPCD function: Description, Usage, Syntax, Examples and Explanation
  • How to calculate compound interest in Excel
  • COUPDAYS function: Description, Usage, Syntax, Examples and Explanation
Acronyms, Abbreviations, Initialism & What They Stand For
© 2021 xlsoffice. All Rights Reserved | Teal Smiles