Question

Agatha takes out a $150, 000 mortgage which is to be repaid in 20 years by monthly payments at 7% convertible monthly. The first payment is due in one month after the mortgage is taken out. After 10 years she refinances the mortgage at 5% convertible monthly and agrees to pay the remaining amount by monthly payments in 5 years. Find the size of her new payment

Please show full detailed steps, thank you!

Answer 1

SEE THE IMAGE. ANY DOUBTS, FEEL FREE TO ASK. THUMBS UP PLEASE

PV, FV, ANNUITY (Autosaved) Microsoft Excel (Product Activation Failed) File Home Insert Page Layout Formulas Data Review View Add-Ins E AutoSum - Cut AC Wrap Text Calibri 11 General E Copy Fill в IU- Merge & Center Insert Delete Format Paste +.0 .00 .00 .0 Conditional Format Cell Sort & Find & % Format Painter Clear Filter Select Formatting as Table Styles Number Clipboard Font Alignment Styles Cells Editing fx FF339 FF FG FH FI FJ FK FL 331 332 LOAN loan amount 333 150000 334 7.00% mortgage rate 335 NO POINTS 0 336 20 years monthly rate 0.583333% 337 no of periods 338 240 339 1162.95 [PMT(FI337, FI338,-FI333)] STEP 1 340 PMT 341 342 LOAN BALANCE AFTER 10 YEARS 343 YEARS LEFT 10 344 NO OF MONTHS LEFT 120 345 100160.51 [PV(FI337,FI344,-F1340)] STEP 2 346 LOAN BALANCE 347 348 NOW REFINANCING 349 loan with points $7 pv, fv, annuity profit margin LOCK BOX s3 CAP STRU VALUE DILUTION DOLLAR COST AVG EXPO NPER APR CPI ear FVA S2 STATE pva Rea 130% (- erences XУ WA 05:21 ENG ll 30-11-2019

PV, FV, ANNUITY (Autosaved) Microsoft Excel (Product Activation Failed) File Home Insert Page Layout Formulas Data Review View Add-Ins E AutoSum - Cut AC Wrap Text Calibri 11 General Copy Fill в IU- Merge & Center Insert Delete Format Paste +.0 .00 .00 .0 Conditional Format Cell Sort & Find & % Format Painter Clear Filter Select Formatting as Table Styles Number Cells Editing Clipboard Font Alignment Styles fx F1359 Formula Bar FL FF FG FH FI FJ 347 348 NOW REFINANCING 349 350 loan amount 100160.51 351 mortgage rate 5.00% 352 5 years monthly rate 0.416667% 353 354 no of periods 60 355 1890.15 PMT(F1353,F1354,-F1350)] 356 PMT 357 358 359 360 361 362 363 364 365 loan with points profit margin LOCK BOX pv, fv, annuity s3 s7 CAP STRU VALUE DILUTION DOLLAR COST AVG EXPO NPER APR CPI s2 STATE ear FVA pva II 130% Rea erences XУ WA 05:22 ENG 30-11-2019

BA PLUS- Microsoft Excel (Product Activation Failed) Review File Home Insert Page Layout Formulas Data View Add-Ins E AutoSum - Cut AC Wrap Text Calibri 11 General ECopy Fill Conditional Format Formatting as Table Styles Merge & Center Insert Delete Format Paste в IU* $ %, +.0 .00 Cell Sort & Find & .00 0 Format Painter Clear Filter Select Number Editing Clipboard Font Alignment Styles Cells fox Н4 A В C D F G H K L М N 2 LOAN loan amount 3 150000 PV 4. 7.00% mortgage rate 5 20 years monthly rate 0.58333333%r 6 no of periods 240 n 7 8 PV PMT*(1/r)*(1-(1/(1+r)^n)) 150000 PMT*(1/0.0058333333)*(1-(1/(1+0.00588333333)^240)) 150000 PMT*128.9825069 9 10 11 PMT 1162.95 12 STEP 1 13 14 LOAN BALANCE AFTER 10 YEARS 15 YEARS LEFT 10 16 NO OF MONTHS LEFT 120 17 PV PMT*(1/r)*(1-(1/(1+r)^n) PV 1162.95*(1/0.0058333333)*(1-(1/(1+0.0058333333)^120)) 18 19 100160.64 20 PV LOAN BALANCE 100160.64 21 STEP 2 22 Sheet4 choice WACC Sheet1 NPV BONDPV FV EAR II Rea erences 120% WA 06:25 ENG 30-11-2019

BA PLUS - Microsoft Excel (Product Activation Failed) Review File Home Insert Page Layout Formulas Data View Add-Ins E AutoSum - Cut AC Wrap Text Calibri 11 General E Copy Fill В IUT Merge & Center Insert Delete Format Paste +.0 .00 Conditional Format Cell Sort & Find & % .00 0 Format Painter Clear Filter Select Formatting as Table Styles Number Editing Clipboard Font Alignment Styles Cells fox F23 A В С E G Н К L М N NOW REFINANCING 23 24 loan amount 100160.64 PV 25 5.00% 26 mortgage rate 27 5 years monthly rate 0.41666667%r 28 no of periods 29 60 n 30 PV PMT*(1/r)*(1-(1/(1+r)^n)) 100160.64 PMT*(1/0.0041666667)*(1-(1/(1+0.004166667)^60)) 100160.64 PMT*52.99070962 31 32 33 34 ANSWER PMT = 1890.15 35 36 37 38 39 40 41 42 43 WACC Sheet1 NPV BONDPV FV Sheet4 choice EAR II Rea erences 120% WA 06:26 ENG 30-11-2019