Most of this article calculates annuity payments for the most common type of annuities: ordinary annuities that make payments at the end of end of the period. Those with annuities that pay at the beginning of the period will have to use the Excel function to calculate their payments. In addition, these calculations assume that the annuity makes payments consistently throughout the term. These calculations will not work for annuities that change interest rates or payment size throughout their lives. [1] X Research source
For example, imagine that you paid $150,000 for an annuity. This would be your principal amount. The duration is how long the annuity pays out payments. For example, this could be 20 years.
For example, you would calculate your monthly interest rate by dividing your annual interest rate, r, by 12. So, for the example, imagine that your annual interest rate is 5 percent. This would be expressed as a decimal for the calculation by dividing by 100 to get 0. 05 (5/100). To get the monthly interest rate, divide this number by 12. So, this would be 0. 05/12, which is 0. 004167. For ease of calculation, we will round this number to 0. 0042. This is the R value that will be later used in the calculation.
For the purposes of this article, the duration is represented by the variable t, the payment frequency by n, and the total number of payments by N. So, for the example, N=240.
p is the annuity payment. P is the principal. R is the period interest rate. N is the total number of payments.
P is $150,000. R is 0. 0042. N is 240.
The completed equation for the example looks like this: p=$150,000∗0. 0042(1+0. 0042)240(1+0. 0042)240−1{\displaystyle p=$150,000*{\frac {0. 0042(1+0. 0042)^{240}}{(1+0. 0042)^{240}-1}}}
After the addition in parentheses, the example is: p=$150,000∗0. 0042(1. 0042)240(1. 0042)240−1{\displaystyle p=$150,000*{\frac {0. 0042(1. 0042)^{240}}{(1. 0042)^{240}-1}}} Next, solve the exponents. This involves raising the lower numbers (1. 0042 in the example) to the power of the higher numbers (240). This is done on a calculator by entering the lower number, pressing the exponent button (usually xy{\displaystyle x^{y}}), and then entering the higher number and pressing enter. The result of the exponent calculation yields 2. 734337. For convenience, we will round this number to 2. 734. So, the example equation now looks like so: p=$150,000∗0. 0042(2. 734)2. 734−1{\displaystyle p=$150,000*{\frac {0. 0042(2. 734)}{2. 734-1}}} Multiply the top of the equation. Multiply the two numbers, 0. 0042 and 2. 734, together. This gives: p=$150,000∗0. 01152. 734−1{\displaystyle p=$150,000*{\frac {0. 0115}{2. 734-1}}} This result, 0. 115, is also a rounded figure. Subtract in the denominator. Complete the figure (2. 734-1). This gives:p=$150,000∗0. 01151. 734{\displaystyle p=$150,000*{\frac {0. 0115}{1. 734}}} Divide the fraction. Divide 0. 0115 by 1. 734 to get 0. 00663206. For convenience, round this number to 0. 00663. The equation is now p=$150,000∗0. 00663{\displaystyle p=$150,000*0. 00663} Solve the final multiplication. Multiply the last two numbers to get the monthly annuity payment, which is $994. 50. Keep in mind that this number is the result of rounded calculations and may be off by several dollars. Keeping more decimals in your calculations will give you a more accurate calculation. In other words, for an annuity costing $150,000 that makes monthly payments based on an annual rate of five percent, you can expect monthly payments of $994. 50.
You should use this method to calculate payments if your annuity is one that makes payments at the beginning of each period (for example the first of the month).
rate is your period interest rate. This is like the monthly interest rate, R, from the calculating by hand method. nper is the number of payments made over the life of the annuity. This is like the total number of payments, N, from the calculating by hand method. pv is the principal of the annuity. This is like the variable P from the calculating by hand method. Don’t worry about the last two prompts, just put a 0 (zero) into each place. [6] X Research source
Make the value for pv a negative number. This represents a payment that you made so it should be negative. [7] X Research source Remember that the monthly interest rate should be input as a decimal. To get this number, divide the stated monthly interest rate by 100, for example 0. 42/100 is 0. 0042. Remember to close the parentheses at the end. Don’t place a comma in your pv value. The program will misread this. The example calculation returns a monthly payment of $993. 25. Note that this number is slightly different than the result calculated by hand in the other method, despite using the same annuity terms for each calculation. This is due to the rounding of figures in the by hand method; the Excel function makes calculations using more decimal places. The actual payment made by the annuity may differ slightly from both of these calculations, depending on the accuracy of the calculation used by the payer.
So for the example, this would be: =PMT(0. 0042, 240,-150000,0,1) This gives a slightly lower payment amount in the example ($989. 10).
