Question

3.

Your dad bought a house for you 10 years ago. He took out a $200,000 mortgage then. The mortgage has a 15year term with monthly payments and has an APR of 8.00%. He paid monthly mortgage for 10 years or 120 months. On October1, 2018, you became the owner of the house and started to be responsible for the rest of the mortgage payments. (Hint: If you continue with the mortgage, you will pay the monthly payment for another 60 months with the first payment due on November 1, 2018.) You are thinking about refinancing the mortgage. On October 1, 2018, how much is the balance of this mortgage?

A) $29,356

B) $66,667

C) $94,262

**The answer is C, but please explain how the answer was derived using excel or a financial calculator, please break it down

Answer 1

Hi!

Caluclating Loan Amottization schedule is easy. Once you learn it, you can keep it as template and work for later sums as well.

FIrst calculate your Monthly EMI. You can use excel for this.

EMi- PMT( ROI /12*tenor*loan amount)

Here - (8%/12*180*200000)

Once you arrive at monthly EMiI use it for our base table which I wil explain below.

Make the follwing columns

Insert Page Layout Formulas Data ReviewView Help Tell me what you want to do Wrap Text 2. AutoSunm Paste u . tti. 2 . . .----A Merge & Center . m. % , s..a Conditional Format as Cell Insert Delete Format FormattingTable Styles Format Painter Clipboard Font Alignment Number Cells Editing EMI I IRR I Principal Interest calculator 3 Loan amount 4 Tenure 5 Interest Rate 6 EMI 7 Month 2,00,000 180 8.00% 1,911 1-Dec-08 In Month Principal Outstanding Mth EMI Principal Interest component_I_component___Principal Cumulative Cumulative Interest 0 1-Dec-08 200000 199422 198840 198255 197665 1-May-09197071 196474 195872 195267 1911 1911 1911 1911 1911 1911 1911 1911 1911 578 582 586 590 594 597 601 605 1333 1329 1326 1322 1318 1314 1310 1306 1302 578 1160 1745 2335 2929 1333 2663 3988 5310 6628 7942 9252 10557 11859 1-Jan-09 1-Feb-09 1-Mar-09 1-Jun-09 1-Jul-09 4128 4733 5343 EMI Calc Sheet5 Sheet4 Sheet3S2 Sheet

You can draw rows till 180

Trick here is to get the first row right, and then you can just drag it till how much ever tenor you want.

Explaining the columns in attached picture-

1st- Tenor

2nd - Date

3rd- Principal Outstanding

4th- Your monthly EMi that remains constant thorught

5thPriincipal component of your EMI- This is calculated once Interest is calculated

6th- interest component

You can Use - prinicpal outsatanding column* $8%$/12- Use cell where you have written *5 and we are using $$ as it remains constant

7th- Cumulative prinicpal- adds the prinicpal component from the first row

Cumulatibe interest- adds the interest component from the first row

-------------------------------------------------------------------------------------------

Next row will be

3rd colmun- 20000- Prinicipal component from 1st row and so on.

Refer my picture and calculate to understand. It will take time to grasp but once it is done, it is a cake walk.

Get back in case you have queries