S 6 7 8 9 10 11 Annual 12 13 Amount 14 Borrowed Amortization Interest Period...

Transcribed Image Text:

S 6 7 8 9 10 11 Annual 12 13 Amount 14 Borrowed Amortization Interest Period in Years Rate (APR) Amortization Period in Years 1-5 Months Years 1-5 15 180000 30 0.06 -B15 12 C15/12 16 Year 6 Year 6 17 0.066 C17/12 18 Year 7 Note: All monthly payments in years 1-5 will be equal, all based on the monthly interest rate computed in cell E14. All monthly payments in year 6 will be equal, all based on the monthly interest rate computed in cell E16. All monthly payments in year 7 will be equal, all based on the monthly interest rate computed in cell E18. Monthly payments in each of years 8-30 will likely differ, based on year-by-year prevailing monthly interest rates. But here we compute all of the year 8-30 monthly payments with the same monthly interest rate expected in year 7. Monthly Interest Monthly Payment Year 1-5 standard formula) |=E15/(1-(1/(1+E15))^D15)*A15 Monthly Payment Year 6 (as computed below) =F90 Monthly Payment Year 7 (as computed below) Rate (APR+12) (based on 19 0.072 -C19/12 -F103 20 21 Monthly 22 Payment 23 (based on 24 Amount Owed remaining Principal 25 Payments Beginning 26 Month Remaining Prin. Bal Plus Interest Before months and Payment balance) Portion of Payment Ending Principal Balance 27 0 =A15 28 A27-1 D15 H27 29 A28-1 B28-1 H28 |=SE$15 C28 SES15 C29 C28-D28 =SE$15/(1-(1/(1+SE$15))^B28)*C28 F28-D28 C28-G28 FC29-D29 =SE$15/(1-(1/(1-SES15)) B29) C29 F29-D29 C29-G29 30-A29-1 B29-1 =H29 |=SESI5*C30 |= 50-250 |=SE$15/(1-(1/(1+SE$15)) "B30)*C30 FF30-D30 C30-G30 31 A30-1 B30-1 -H30 SE$15 C31 |=C31-D31 |=SE$15/(1-(1/(1+$E$15))^B31)*C31 F31-D31 |=C31-G31 32 A31-1 B31-1 H31 SES15 C32 C32+D32 |=$E$15/(1-(1/(1+$E$15))^B32)*C32 F32-D32 -C32-G32 33-A32-1 B32-1 H32 =SES15 C33 C33-D33 |=SE$15/(1-(1/(1+SE$15))"B33)*C33 F33-D33 C33-G33 86 (continue pasting down) 87 AS5-1 B85-1 H85 ESES15 C$7 C$7-D87 |=SE$15/(1-(1/(1+SE$15))^B$7)*C87 F87-DS7 C87-G87 88 A87-1 -B87-1 HS7 =SE$15 CSS CSS-DSS =SE$15/(1-(1/(1+SE$15)) BSS)*CSS FSS-DSS CSS-GSS 89 (note change in interest rate) 90 ASS-1 BSS-1 HSS ESES17 C90 C90-D90 |=SE$17/(1-(1/(1+SE$17))^B90)*C90 F90-D90 C90-G90 91-A90-1 B90-1 -H90 =SES17 C91 C91+D91 |=SE$17/(1-(1/(1+SE$17))^*B91)*C91 F91-D91 C91-G91 92 A91-1 =891-1 -H91 =SES17 C92 C92-D92 |=SE$17/(1-(1/(1+SES17)) "B92)*C92 F92-D92 -C92-G92 93 -A92-1 B92-1 HO2 SES17 C93 C93-D93 SE$17/(1-(1/(1-SE$17))"B93)*C93 F93-D93 C93-G93 94 -A93-1 =B93-1 HO3 ESES17 C94 C94-D94 =SE$17/(1-(1/(1+SE$17)) B94)*C94 F94-D94 C94-G94 95 A94-1 -B94-1 -H94 SE$17 C95 C95-D95 |=SE$17/(1-(1/(1+SE$17))"B95)*C95 F95-D95 -C95-G95 96 A95-1 -895-1 -H95 =SES17 C96 C96+D96 |=SE$17/(1-(1/(1+SE$17))^B96)*C96 =F96-D96 C96-G96 97 -A96-1 B96-1 -H96 =SES17 C97 -C97+D97 |=SE$17/(1-(1/(1+SE$17))^B97)*C97 |=F97-D97 C97-G97 98 A97-1 -897-1 H97 =SES17 C98 C98-D98 =SE$17/(1-(1/(1+SE$17))^B98)*C98 F98-D98 C98-G-98 99 A98-1 =B98-1 HOS ESES17 C99 C99-D99 =SE$17/(1-(1/(1-SES17)) B99) C99 F99-D99 C99-G99 100-A99-1 B99-1 66H- SE$17 C100 C100-D100 SES17/(1-(1/(1+SE$17)) B100) C100 F100-D100 C100-G100 101-A100+1 B100-1 H100 ESES17 C101 =C101-D101 |=SE$17/(1-(1/(1+SE$17))"B101)*C101 FF101-D101 |=C101-GII 102 (note change in interest rate 103-A101-1 |=3101-1 -H101 =SES19 C103 =C103-D103 |=SE$19/(1-(1/(1+SE$19))*B103)*C103 F103-D103 C103-G103 104-A103-1 B103-1 H103 SES19 C104 C104-D104 |=SE$19/(1-(1/(1+SE$19))"B104)*C104 FF104-D104 C104-G104 384 (continue pasting down) 385-A383+1 -B383-1 H383 =SES19 C385 =C385+D385 =SE$19/(1-(1/(1+SE$19)) B385)*C385 F385-D385 =C385-G385 386-A385+1 B385-1 H385 SES19 C386 C386-D386 =SE$19/(1-(1/(1+SES19)) B386) C386 FF386-D386 C386-G386 387-A386+1 B386-1 H386 =SES19 C387 =C387-D387 -SE$19/(1-(1/(1+SES19)) B387)*C387 -F387-D387 C387-G387 388-A387-1 B387-1 -H387 =SES19 C388 =C388-D388 |=SES19/(1-(1/(1+SE$19)) B388) C388 F388-D388 =C388-G388 389-A388-1 B388-1 H3SS -SES19 C389 -C389-D389 |=SE$19/(1-(1/(1+SE$19))"B389)*C389 F389-D389 C389-G389 390=A389+1 B389-1 H389 =SES19 C390 C390-D390 |=SE$19/(1-(1/(1+SES19))"B390)*C390 FF390-D390 C390-G390 391-A390+1 B390-1 -H390 SES19 C391 C391-D391 |=SE$19/(1-(1/(1+SE$19))^B391)*C391 F391-D391 C391-G391 392 393 ESUMD28 D391) =SUM(F28 F391) ESUM(G28:G391) 394 Total Total 395 Interest 396 Paid Payments Made Total Principal Repaid S 6 7 8 9 10 11 Annual 12 13 Amount 14 Borrowed Amortization Interest Period in Years Rate (APR) Amortization Period in Years 1-5 Months Years 1-5 15 180000 30 0.06 -B15 12 C15/12 16 Year 6 Year 6 17 0.066 C17/12 18 Year 7 Note: All monthly payments in years 1-5 will be equal, all based on the monthly interest rate computed in cell E14. All monthly payments in year 6 will be equal, all based on the monthly interest rate computed in cell E16. All monthly payments in year 7 will be equal, all based on the monthly interest rate computed in cell E18. Monthly payments in each of years 8-30 will likely differ, based on year-by-year prevailing monthly interest rates. But here we compute all of the year 8-30 monthly payments with the same monthly interest rate expected in year 7. Monthly Interest Monthly Payment Year 1-5 standard formula) |=E15/(1-(1/(1+E15))^D15)*A15 Monthly Payment Year 6 (as computed below) =F90 Monthly Payment Year 7 (as computed below) Rate (APR+12) (based on 19 0.072 -C19/12 -F103 20 21 Monthly 22 Payment 23 (based on 24 Amount Owed remaining Principal 25 Payments Beginning 26 Month Remaining Prin. Bal Plus Interest Before months and Payment balance) Portion of Payment Ending Principal Balance 27 0 =A15 28 A27-1 D15 H27 29 A28-1 B28-1 H28 |=SE$15 C28 SES15 C29 C28-D28 =SE$15/(1-(1/(1+SE$15))^B28)*C28 F28-D28 C28-G28 FC29-D29 =SE$15/(1-(1/(1-SES15)) B29) C29 F29-D29 C29-G29 30-A29-1 B29-1 =H29 |=SESI5*C30 |= 50-250 |=SE$15/(1-(1/(1+SE$15)) "B30)*C30 FF30-D30 C30-G30 31 A30-1 B30-1 -H30 SE$15 C31 |=C31-D31 |=SE$15/(1-(1/(1+$E$15))^B31)*C31 F31-D31 |=C31-G31 32 A31-1 B31-1 H31 SES15 C32 C32+D32 |=$E$15/(1-(1/(1+$E$15))^B32)*C32 F32-D32 -C32-G32 33-A32-1 B32-1 H32 =SES15 C33 C33-D33 |=SE$15/(1-(1/(1+SE$15))"B33)*C33 F33-D33 C33-G33 86 (continue pasting down) 87 AS5-1 B85-1 H85 ESES15 C$7 C$7-D87 |=SE$15/(1-(1/(1+SE$15))^B$7)*C87 F87-DS7 C87-G87 88 A87-1 -B87-1 HS7 =SE$15 CSS CSS-DSS =SE$15/(1-(1/(1+SE$15)) BSS)*CSS FSS-DSS CSS-GSS 89 (note change in interest rate) 90 ASS-1 BSS-1 HSS ESES17 C90 C90-D90 |=SE$17/(1-(1/(1+SE$17))^B90)*C90 F90-D90 C90-G90 91-A90-1 B90-1 -H90 =SES17 C91 C91+D91 |=SE$17/(1-(1/(1+SE$17))^*B91)*C91 F91-D91 C91-G91 92 A91-1 =891-1 -H91 =SES17 C92 C92-D92 |=SE$17/(1-(1/(1+SES17)) "B92)*C92 F92-D92 -C92-G92 93 -A92-1 B92-1 HO2 SES17 C93 C93-D93 SE$17/(1-(1/(1-SE$17))"B93)*C93 F93-D93 C93-G93 94 -A93-1 =B93-1 HO3 ESES17 C94 C94-D94 =SE$17/(1-(1/(1+SE$17)) B94)*C94 F94-D94 C94-G94 95 A94-1 -B94-1 -H94 SE$17 C95 C95-D95 |=SE$17/(1-(1/(1+SE$17))"B95)*C95 F95-D95 -C95-G95 96 A95-1 -895-1 -H95 =SES17 C96 C96+D96 |=SE$17/(1-(1/(1+SE$17))^B96)*C96 =F96-D96 C96-G96 97 -A96-1 B96-1 -H96 =SES17 C97 -C97+D97 |=SE$17/(1-(1/(1+SE$17))^B97)*C97 |=F97-D97 C97-G97 98 A97-1 -897-1 H97 =SES17 C98 C98-D98 =SE$17/(1-(1/(1+SE$17))^B98)*C98 F98-D98 C98-G-98 99 A98-1 =B98-1 HOS ESES17 C99 C99-D99 =SE$17/(1-(1/(1-SES17)) B99) C99 F99-D99 C99-G99 100-A99-1 B99-1 66H- SE$17 C100 C100-D100 SES17/(1-(1/(1+SE$17)) B100) C100 F100-D100 C100-G100 101-A100+1 B100-1 H100 ESES17 C101 =C101-D101 |=SE$17/(1-(1/(1+SE$17))"B101)*C101 FF101-D101 |=C101-GII 102 (note change in interest rate 103-A101-1 |=3101-1 -H101 =SES19 C103 =C103-D103 |=SE$19/(1-(1/(1+SE$19))*B103)*C103 F103-D103 C103-G103 104-A103-1 B103-1 H103 SES19 C104 C104-D104 |=SE$19/(1-(1/(1+SE$19))"B104)*C104 FF104-D104 C104-G104 384 (continue pasting down) 385-A383+1 -B383-1 H383 =SES19 C385 =C385+D385 =SE$19/(1-(1/(1+SE$19)) B385)*C385 F385-D385 =C385-G385 386-A385+1 B385-1 H385 SES19 C386 C386-D386 =SE$19/(1-(1/(1+SES19)) B386) C386 FF386-D386 C386-G386 387-A386+1 B386-1 H386 =SES19 C387 =C387-D387 -SE$19/(1-(1/(1+SES19)) B387)*C387 -F387-D387 C387-G387 388-A387-1 B387-1 -H387 =SES19 C388 =C388-D388 |=SES19/(1-(1/(1+SE$19)) B388) C388 F388-D388 =C388-G388 389-A388-1 B388-1 H3SS -SES19 C389 -C389-D389 |=SE$19/(1-(1/(1+SE$19))"B389)*C389 F389-D389 C389-G389 390=A389+1 B389-1 H389 =SES19 C390 C390-D390 |=SE$19/(1-(1/(1+SES19))"B390)*C390 FF390-D390 C390-G390 391-A390+1 B390-1 -H390 SES19 C391 C391-D391 |=SE$19/(1-(1/(1+SE$19))^B391)*C391 F391-D391 C391-G391 392 393 ESUMD28 D391) =SUM(F28 F391) ESUM(G28:G391) 394 Total Total 395 Interest 396 Paid Payments Made Total Principal Repaid