Suppose the principal of an endowment is known, along with the monthly payment amount, and how many years it is expected to run. How does one therefore calculate the interest rate that the principal is earning, to be able to finance the monthly distributions?
Unfortunately, the formula that relates interest rate to the number of periodic payments and endowment amount is only solvable with a computer, except in the special case of an endowment paying in perpetuity. Fortunately, financial calculators and spreadsheets can perform this task.
- Skill level:
Other People Are Reading
Things you need
- Spreadsheet software
- Financial calculator
Enter the endowment amount. Press the "Minus" sign, then press the "PV" key. This will enter the endowment amount as a present value, effectively treating the endowment amount as an outstanding loan being paid down. It is negative, so the future value is zero after making positive payments.
Enter the number of years left in the endowment payments. Multiply this number by 12 to get the number of monthly payments left. Hit the "Equals" sign. Then press the "N" key to enter this number as the number of remaining monthly payments.
Enter the monthly amount paid out by the endowment. Press the "PMT" key.
Press the "CMPT" key, then the "i" key; the CMPT key need not be held down when pressing the I key. The screen will then go blank for a second while the calculator performs the numerical computation. The result will be the monthly interest rate. To get the annual interest rate, follow Step 5.
Compute the annual interest rate by solving (1 + i)^12 = 1 + j. Here, I represents the monthly interest rate calculated in Step 4; j represents the annual interest rate, assuming compounding of interest.
Enter the number of payments into cell A1. If payments are monthly, the number of payments, N, is 12 times the number of years the endowment will continue to make payouts.
Enter the monthly payment amount into cell A2.
Enter the remaining endowment amount into cell A3.
Enter the following syntax into A4: = RATE(A1,A2,-A3). This will give you the monthly interest rate.
Enter the following into A5: = (1 + A4)^12 -- 1 to get the annual interest rate.
Tips and warnings
- The resource titled "How to Calculate Interest Numerically" has computer code for solving for the interest rate. It uses the bisection method of numerical analysis, which is sufficient, and preferable to taking N derivatives for the Newton-Raphson method.
- If the endowment exists in perpetuity, then the above approaches won't work because the number of payments N is infinite. In that case, each monthly payment equals the interest earned in that month by the endowment, i.e., the monthly payment has no principal component. There is no increase or decrease of the endowment amount. Therefore, the formula for the interest rate reduces to a simple formula: interest = monthly payment ÷ endowment amount. Note that this interest is per month; therefore, the annual rate is found by solving (1 + monthly interest rate) ^ 12 = 1 + annual rate, if compounding is performed monthly.
- 20 of the funniest online reviews ever
- 14 Biggest lies people tell in online dating sites
- Hilarious things Google thinks you're trying to search for