mortgage calculation: don't understand example calculation

[vugar]

hi

i'm doing my uni work, and i have this example spreadsheet of mortgage principal reduction
unfortunately i don't understand the idea behind it

here is some data:
initial mortgage amount is 385000
interest rate is 7%
number of years 15

I would calculate it as:
anual interest is: 385000*0.07=26950
annual payments=monthly payments*12 - which is 41526 (I have used P = PV × r/1− (1+r)^−n) or we can use pmt function on excel
then i subtracted annual interest 26950 from total annual mortgage repayment 41526 to get a principal amount which was repaid 14576
then I minused from original loan amount 385000 the principal amount repaid 14576, and I get 370424 of outstanding balance.

I dont know how this guys calculated like this, i have shared the spreadsheet on google docs - link is below

thank you everyone in advance

https://docs.google.com/spreadsheets/d/17McrL1loKlDzzBSiA0EF8jy_BJODZ4q6JfE9vR6xZik/edit?usp=sharing

 

[vugar]

please explain

The monthly payment of 3,460 and end of year#1
balance owing of 369,947 shown on attachment
are both CORRECT.


NO, you cannot do that.
You need to apply MONTHLY payments (12 times each year).
The rate to be used is .07 / 12.

7% interest is an annual interest rate, it isnt a monthly, why I can not do this

https://www.moneyadviceservice.org.uk/en/articles/interest-rates-explained

can you please explain to me the whole calculation, why they have calculated this way?

many thanks.

what is your background by the way

 

[vugar]

can you please explain the calculation and why they have done this way

also here is the link which says that annual interest rate can be calculated by multiplying loan to interest rate

https://www.moneyadviceservice.org.uk/en/articles/interest-rates-explained

why did you say I cant do this way?

 

[Ishuda]

hi

i'm doing my uni work, and i have this example spreadsheet of mortgage principal reduction
unfortunately i don't understand the idea behind it

here is some data:
initial mortgage amount is 385000
interest rate is 7%
number of years 15

I would calculate it as:
anual interest is: 385000*0.07=26950
annual payments=monthly payments*12 - which is 41526 (I have used P = PV × r/1− (1+r)^−n) or we can use pmt function on excel
then i subtracted annual interest 26950 from total annual mortgage repayment 41526 to get a principal amount which was repaid 14576
then I minused from original loan amount 385000 the principal amount repaid 14576, and I get 370424 of outstanding balance.

I dont know how this guys calculated like this, i have shared the spreadsheet on google docs - link is below

thank you everyone in advance

https://docs.google.com/spreadsheets/d/17McrL1loKlDzzBSiA0EF8jy_BJODZ4q6JfE9vR6xZik/edit?usp=sharing

To expand a little on what Denis said, for your first payment you paid one months interest of about 2246 and principal of about 1214 so that for the second month you were paying interest on about 383786 and not the full 385000.

So, for the second month your interest was less at about 2239 and principal reduction was more at about 1221 so that for the third month you were paying interest on about 382565 and not the full 385000.

...

EDIT: Fix dumb misteak

 

[vugar]

thanks ishida

I still do not understand how he amortised the loan annualy
where these method come from

can you explain this calculation for me