2

Below, I have a cash flow chart showing the loan received ($30000) at the beginning, and three equal payments (amount we want to know [x on chart]), based on their period, for reimbursement of loan:

  • First payment at the end of first year;
  • Second payment at the end of third year;
  • and last payment at end of sixth year.

Using different interest rates on periods:

  • 5% for the first two years;
  • 3% for next three years;
  • and 8% for final year.

Cash flow chart

Using software like Excel or Numbers, how to calculate equal payments based on different interest rates?

Thank you!

2

Assuming the interest rates that you are showing are Annual Percentage Rates so that we don't need to worry about compounding interest monthly, just yearly, the value of X, the equal payments at the end of the first, third, and sixth years, is the solution to the following equation.

((30000(1.05) - X)(1.05)(1.03) - X)(1.03)(1.03)(1.08) - X = 0

which is 11430.21 unless I made a calculation error. The number is smaller than the one given by mbhunter since I am assuming APRs and compounding annually rather than compounding monthly. Note that 5% per annum compounded monthly, as in mbhunter's answer, is an APR of (1 + 0.05/12)^12 - 1 or 5.116%. Replacing each 1.0y in the above equation by (1 + 0.0y/12)^12 should give mbhunter's answer.

1

I came up with $11,531.52 for the payment.

I used OpenOffice Calc.

  1. Put the payment amount in cell F1.
  2. I compounded monthly, and made the payment at the end of the month. (Why not?)
  3. Column A has the principal at the beginning of the month indicated by the row number. (Start in the upper left.) The loan is paid off at the beginning of month 73 (in row 73).
  4. Column B had the annual rate as a fraction (.05, .03. .08). Months 1-24 had .05; months 25-60 had .03, and months 61-72 had .08.
  5. Column C had the principal amount at the end of the month: Column A times (1 + Column B)^(1/12).
  6. Column D had the payment amount at the end of that month. The value is zero except for months 12, 36, and 72, which instead had the value shown in cell F1 (the payment).
  7. Cell A1 contains the initial principal amount (30000). Cell A2 contains "=C1-D1" which is the principal at the end of the month, minus any payment.
  8. Do a Fill ... Down from cells B1:C1, down to B72:C72.
  9. Do a Fill ... Down from cell A2 down to A73.
  10. Play with the amount in F1 until A73 is as close to zero as you can get it.

That should get you close. Season to taste.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.