1
$\begingroup$

So I found this example in the net

Compound interest formula (including principal):

A = P(1+r/n)(nt)

If an amount of $5,000 is deposited into a savings account at an annual interest rate of 5%, compounded monthly, the value of the investment after 10 years can be calculated as follows...

P = 5000. r = 5/100 = 0.05 (decimal). n = 12. t = 10.

If we plug those figures into the formula, we get:

A = 5000 (1 + 0.05 / 12) ^ (12(10)) = 8235.05.

So, the investment balance after 10 years is $8,235.05.

Okay, I'm using the PMT calculator in excel and I plug in these values

enter image description here

I get 53.03\$ per month, okay if I multiply it by 12*10 years I should get the same amount as the site, but now I get 6 363,93$

Why not the same? I use the pmt function in excel

Improve this question
$\endgroup$
0

2 Answers 2

Reset to default
1
$\begingroup$

The reason of this difference is that you do not fully understand what Compound interest is.

Actually, if you want to get the same amount as the site, while using your monthly payment, which is almost correct (more about this "almost" thereafter), what you must do is less straightforward than simply multiplying it by $120$ (calculation which would return the correct value in the $0$ interest rate case.)

What you must understand is that, the first monthly payment, let call it $P_0$, is going to capitalize (at a $\frac{5}{12}\%$ rate) for $120$ months, $P_1$ during $119$ months, $...$ and $P_{119}$ during $1$ month. And this is the sum of all these differently capitalized identical payments that you must compute to get $8235.05$.

That being said, if you do so, you will get $8263.36$ instead of $8235.05$, since the payment is actually due at the beginning of the period (whence the term almost above), i.e. you must set a $1$ instead of $0$ for the argument Type, which will return a monthly flow of $(\$52.81)$.

Thus, to summarize

$8235.05 = 52.8127046833568 \times \sum_{i=0}^{119} \left( 1+\frac{.05}{12} \right)^{120-i} \approx 52.813 \times 155.9292889 $

Where $155.9292889$ can be interpreted as an implicit capitalization factor.

Improve this answer
$\endgroup$
1
  • $\begingroup$ Any question @Steve ? $\endgroup$
    – keepAlive
    Oct 17, 2019 at 11:38
-1
$\begingroup$
  1. Get the Future Value: $8235.05 2. Divide it by 120 to get payment per month: $68.63
  2. Calculate Monthly Interest: 5000 * (5/12)
  3. Change formula in "Balance" column to previous balance + interest (5000 + 20.83)
  4. Copy down to row 120

Yes, you do fully understand compounding but putting it into an amortized plan is not quite intuitive

enter image description here

Improve this answer
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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