The Annuity Calculator on this page is based on the time-value-of-money or “finance theory” definition of annuity. By that definition, an annuity is a series of fixed payments over a certain amount of time. This annuity calculator was not designed to analyze an Insurance Annuity which can mean something entirely different from the finance theory definition.
Also, Check out retirement lifetime savings planner excel template by ExcelDownloads. This annuity plan calculator helps you manage your personal finance and client’s finance. This is useful for personal and professional use.
About Annuity Plan Calculator free excel templates
This Annuity Calculator aims to answer general questions related to taking a withdrawal or annuity payment from a fixed-rate savings account. Example questions might be:
- What is the withdrawal or annuity payment per year over n years if I start with $P?
- What do I need to have saved for retirement in order to withdraw $A per year for n years?
- How many years can I withdraw $A if I start with $P?
This Annuity Calculator spreadsheet contains two worksheets. The first worksheet shows a yearly cash flow table and a graph of the balance.
It solves for the annuity payment amount. In the second worksheet shown in the screenshot on the left, you can solve for the annuity payment, the initial principal, or the number of years to pay out.
Both worksheets allow you to specify an annual inflation rate which will cause the withdrawal or annuity payment to increase over time.
Annuity Calculator Formulae
As a financial term used for time-value of money calculations, an annuity is the name given to the uniform series cash flow. It is represented as a payment of amount A starting at t=1 and remaining constant through t=n, as shown in the cash flow diagram in figure 1 below.
Fig 1. Uniform Series or Annuity Cash Flow (the same payment amount A from t=1 to t=n)
Fig 2. Exponential Gradient Series Cash Flow (g might be the inflation rate for example)
The annuity represented in figure 1 is called an Ordinary Annuity, or an annuity in which the payments are made at the end of each period.
Monthly mortgage payments are an example of an ordinary annuity. An Annuity Due (see Fig 3) is one in which the payments are made at the beginning of each period. Examples of annuities due might be deposits in savings, retirement withdrawals, rent payments, or insurance premiums.
Fig 3. Cash flow diagram for an Annuity Due
The Excel functions PMT, PV, FV, and NPER can handle both types of annuities. These functions are all inter-related, based on the equivalency formula below, where type is used to identify the type of annuity (0 for an ordinary annuity or 1 for an annuity due).
Fig 4. From the Excel Help documentation for the PV function
An inflation-adjusted annuity payment, something you might want to consider for retirement withdrawals, would actually be termed an exponential gradient series in finance theory.
The cash-flow diagram for the exponential gradient series is shown in Figure 2.
To print the report in PDF or Physical document for management review, you can simply print this document. We have a separate tutorial blogpost on how to print worksheet or workbook in excel. Refer >> How to print Excel File ? tutorial.
How to download and use Annuity Calculator free Excel template ?
To use this free Annuity Calculator excel template, you should have Microsoft Office/ Microsoft Excel installed in your system. After installing Excel or Spreadsheet, download the zip file of this template, extract the template using WinRAR or 7Zip decompressing software.
Once extracted, you can open the file using Excel and start entering data or customizing the template.
