0

I have created a table of repayment schedule of a loan amount \$20,000 at 12% annual interest rate and a repayment of \$500 each month using Excel. The balance, using the first month as an example, is calculated as:

($20,000 - $500)*(1+12%/12)=$19,695.
Month Payment Balance
1 500 19,695.00
2 500 19,386.95
3 500 19,075.82
4 500 18,761.58
5 500 18,444.19
6 500 18,123.64
7 500 17,799.87
8 500 17,472.87
9 500 17,142.60
10 500 16,809.03
11 500 16,472.12
12 500 16,131.84
13 500 15,788.15
14 500 15,441.04
15 500 15,090.45
16 500 14,736.35
17 500 14,378.71
18 500 14,017.50
19 500 13,652.68
20 500 13,284.20
21 500 12,912.05
22 500 12,536.17
23 500 12,156.53
24 500 11,773.09

Although I can always update the table in Excel, but I wonder if there are formulas to quickly find out:

  1. Given load amount \$20,000, monthly repayment \$500 and the remaining balance \$11,773.09 after the 24th repayment, can you find out the annual interest rate?

  2. Given load amount \$20,000, annual interest rate 12% and the remaining balance \$11,773.09 after the 24th repayment, can you find out the monthly repayment amount?

Edited: I can answer 2. myself:

Monthly repayment amount = ($20,000*(1+12%/12)^24-$11,773.09)*(12%/12)/(1+12%/12)/((1+12%/12)^24-1)
user1589188
  • 111
  • 1
    Welcome to MSE. Your question is phrased as an isolated problem, without any further information or context. This does not match many users' quality standards, so it may attract downvotes, or be closed. To prevent that, please [edit] the question. This will help you recognize and resolve the issues. Concretely: please provide context, and include your work and thoughts on the problem. These changes can help in formulating more appropriate answers. – José Carlos Santos Jan 30 '24 at 12:54
  • 1
    (see https://math.libretexts.org/Courses/College_of_the_Canyons/Math_100%3A_Liberal_Arts_Mathematics_(Saburo_Matsumoto)/03%3A_Mathematics_and_Finance/3.02%3A_Annuities_and_Loans) The balance after $N$ years is $$ P_N = \frac{d \left( \left( 1+ \frac{r}{k} \right)^{Nk} - 1 \right)}{ \left( \frac{r}{k} \right) } $$ and if you want to calculate the interest rate, you would have to solve for $r$. This can be done iteratively/numerically, but I don't think that you can reverse the formula. – Matti P. Jan 30 '24 at 13:17
  • @MattiP. Thanks, but the one you quoted is about interest accumulated, while my question is about loan repayment, your information doesn't answer my question. – user1589188 Jan 31 '24 at 04:45
  • @user1589188 -- BTW, what you show below "I can answer 2. myself" is the result if computed at the BEGIN of the period. Pls confirm. – m-stgt Jan 31 '24 at 06:59
  • @m-stgt yes I can confirm my formula is correct (it computes to $500) and is the answer I seek. I can't confirm what you mean though. – user1589188 Jan 31 '24 at 07:12
  • @user1589188 -- Payments can be made either at the beginning of a compounding period (payments in advance, or annuities due) or at the end of the period (payments in arrears, or ordinary annuities). Now please confirm the formula you show is for the payment mode BEGIN. – m-stgt Jan 31 '24 at 07:44
  • 1
    @m-stgt Ah ok. In that sense then yes, its before. – user1589188 Feb 01 '24 at 03:21

1 Answers1

1

I hope you don't mind if I answer indirecly instead of just presenting a result.
For machines running under Windows (what includes VM thereof), there is an "emulator" of the HP12C for download, the corresponding manual you may find here (Appendix D, Formulas Used, shows all used formulas). This virtual calculator offers an acknowledged standard of TVM (time-value of money) what enables you to answer your question in quite short time on your own. HTH

m-stgt
  • 301
  • Thanks. Good to know there is some calculator exists to help getting the value. But I am after a formula so that I can use it in programming to compute the answer directly. – user1589188 Jan 31 '24 at 07:14
  • @user1589188 -- You'll find all formulas in Appendix D of the a. m. manual. – m-stgt Jan 31 '24 at 07:34