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

<p>Actually, if you want to get the same amount as the site, while using your monthly payment, which is <em>almost</em> 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.)</p>

<p>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$. </p>

<p>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 <em>almost</em> above), i.e. you must set a $1$ instead of $0$ for the argument <strong>Type</strong>, which will return a monthly flow of $(\$52.81)$. </p>

<p>Thus, to summarize</p>

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

<p>Where $155.9292889$ can be interpreted as an implicit capitalization factor.</p>

CC BY-SA 4.0
2019-10-17T11:38:05.270Z
Any question @Steve ?
keepAlive
CC BY-SA 3.0
2017-07-21T01:07:25.277Z
2017-07-21T13:34:44.000Z
2017-07-21T13:34:44.000Z
keepAlive
<ol>
<li>Get the Future Value: <span class="math-container">$8235.05
2. Divide it by 120 to get payment per month: $</span>68.63</li>
<li>Calculate Monthly Interest: 5000 * (5/12)</li>
<li>Change formula in "Balance" column to previous balance + interest (5000 + 20.83)</li>
<li>Copy down to row 120</li>
</ol>

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

<p><a href="https://i.sstatic.net/XhxoJ.png" rel="nofollow noreferrer"><img src="https://i.sstatic.net/XhxoJ.png" alt="enter image description here"></a></p>

CC BY-SA 4.0
2019-08-16T00:33:07.857Z
2019-08-16T00:33:07.857Z
Khurram
<p>So I found this example in the net</p>

<blockquote>
  <p>Compound interest formula (including principal):</p>
  
  <p>A = P(1+r/n)(nt)</p>
  
  <p>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>
  
  <p>P = 5000. r = 5/100 = 0.05 (decimal). n = 12. t = 10.</p>
  
  <p>If we plug those figures into the formula, we get:</p>
  
  <p>A = 5000 (1 + 0.05 / 12) ^ (12(10)) = 8235.05.</p>
  
  <p>So, the investment balance after 10 years is $8,235.05.</p>
</blockquote>

<p>Okay, I'm using the PMT calculator in excel and I plug in these values </p>

<p><a href="https://i.sstatic.net/65GkI.png" rel="nofollow noreferrer"><img src="https://i.sstatic.net/65GkI.png" alt="enter image description here"></a></p>

<p>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$</p>

<p>Why not the same? I use the pmt function in excel</p>

<interest-rate><finance><discount>
Compound interest problem using PMT in Excel
Steve
# Compound interest problem using PMT in Excel

(Asked by: username_0 on 2017-07-20)

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](https://i.sstatic.net/65GkI.png)](https://i.sstatic.net/65GkI.png)

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

**Tags:** interest-rate, finance, discount

---

## 2 answers

### Answer by username_1 on 2017-07-21. Score: 1

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.

> **Comments:**
> **username_1**: Any question @Steve ?  

---

### Answer by username_2 on 2019-08-16. Score: -1

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](https://i.sstatic.net/XhxoJ.png)](https://i.sstatic.net/XhxoJ.png)


---

