Question

8. *Eight years ago you borrowed $200,000 to finance the pur- chase of a $240,000 home. The interest rate on the old mort- gage loan is 6 percent. Payments are being made monthly to amortize the loan over 30 years. You have found another lender who will refinance the current outstanding loan balance at 4 percent with monthly payments for 30 years. The new lender

please show all work!

Answer 1

	a	Payment on Old Loan
	Pv	Old Loan amount	$200,000
	Rate	Monthly interest rate =(6/12)%	0.5%
	Nper	Number of months of amortization=30*12	360
	PMT	Monthly Payment on old Loan	$1,199.10	(Using PMT function of excel with Rate =0.5%, Nper=360, Pv=-200000)
	b	Current Loan Balance on Old Loan
	Pmt	Monthly Payment on old Loan	$1,199.10
	Rate	Monthly interest rate =(6/12)%	0.5%
	Nper	Number of months of payments made=8*12	96
	FV	Future Value of Past Payments Today	$147,284	(Using FV function of excel with Rate =0.5%, Nper=96, Pmt=-1199.10)
	FV1	Future Value of Old Loan Today	$322,829	(Using FV function of excel with Rate =0.5%, Nper=96, Pv=-200000)
	FV1-FV	Current Loan Balance on Old Loan	$175,545
	c	Monthly Payment on NEW Loan
	Pv	New Loan amount	$175,545
	Rate	Monthly interest rate =(4/12)%	0.3333%
	Nper	Number of months of amortization=30*12	360
	PMT	Monthly Payment on old Loan	$838.08	(Using PMT function of excel with Rate =0.3333%, Nper=360, Pv=-175545)
	d	New Loan Outstanding for 5 years
		OLD Loan Balance after 5 years:
	FV2	Future Value of Monthly Payments after 5 years(60 months)	$83,661	(Using FV function of excel with Rate =0.5%, Nper=60, Pmt=-1199.10)
	FV3	Future Value of Old Loan After 5 years	$236,784	(Using FV function of excel with Rate =0.5%, Nper=60, Pv=-175545)
	FV3-FV4	Terminal Payment on old loan at the end of 5 years	$153,122
	FV5	Future Value of NEW Loan Monthly paymentsAfter 5 years	$55,564	(Using FV function of excel with Rate =0.3333%, Nper=60, Pmt=-838.08)
	FV6	Future Value of NEW   Loan After 5 years	$214,339	(Using FV function of excel with Rate =0.3333%, Nper=60, Pv=-175545)
	FV6-FV5	Terminal Payment on NEW loan at the end of 5 years	$158,776
	Fv	Additional Terminal Payment for New Loan=158776-153122	$5,654
	Pmt	Savings in monthly payment for new loan=1199.10-838.08	$361.02
	PV	Present Value of total Savings for 60 months	$14,973	(Using PV function of excel with Rate =0.3333%, Nper=60, Pmt=-361.02, Fv=5654)
		Initial Outlay for New Loan:
		2 discount Points=2%*175545=	$3,511
		Other refinancing costs	$6,000
		Total Initial Outlay	$9,511
		NET SAVINGS FROM NEW LOAN =14973-9511	$5,462
		YES, YOU SHOULD REFINANCE
				H12 x fc - A =FV(H10, H11,-H9) D E 1 F B C HT J K L M N O Pv Rate Nper PMT Payment on Old Loan Old Loan amount Monthly interest rate =(6/12)% Number of months of amortization=30*12 Monthly Payment on old Loan $200,000 0.5% 360 $1,199.10 (Using PMT function of excel with Rate =0.5%, Nper=360, PV=-200000) b Pmt Rate Nper FV Current Loan Balance on Old Loan Monthly Payment on old Loan Monthly interest rate =(6/12)% Number of months of payments made=8*12 Future Value of Loan Today $1,199.10 0.5% 96 $147,284 | (Using FV function of excel with Rate =0.5%, Nper96, Pmt=-1199.10) H13 for =FV(H10,H11,-43) D E A B C F H I J K L M N O a Pv Rate Nper PMT Payment on Old Loan Old Loan amount Monthly interest rate =(6/12)% Number of months of amortization=30*12 Monthly Payment on old Loan $200,000 0.5% 360 $1,199.10 (Using PMT function of excel with Rate =0.5%, Nper=360, Pv=-200000) $1,199.10 0.5% b Pmt Rate Nper FV FV1 Current Loan Balance on Old Loan Monthly Payment on old Loan Monthly interest rate =(6/12)% Number of months of payments made=8*12 Future Value of Past Payments Today Future Value of Old Loan Today | 96 $147,284 $322,829 (Using FV function of excel with Rate =0.5%, Nper96, Pmt=-1199.10) (Using FV function of excel with Rate =0.5%, Nper96, Pv=-200000) H24 - x fc A B C D H I J K L M N O O-Nm =FV(0.5%,60,-H9) E F Pmt Rate Nper FV FV1 FV1-FV Monthly Payment on old Loan Monthly interest rate =(6/12)% Number of months of payments made=8*12 Future Value of Past Payments Today Future Value of Old Loan Today Current Loan Balance on Old Loan $1,199.10 0.5% 96 $147,284 $322,829 $175,545 (Using FV function of excel with Rate =0.5%, Nper96, Pmt=-1199.10) (Using FV function of excel with Rate =0.5%, Nper=96, Pv=-200000) o o o o PV Rate Nper Monthly Payment on NEW Loan New Loan amount Monthly interest rate =(4/12)% Number of months of amortization=30*12 Monthly Payment on old Loan $175,545 0.3333% 360 $838 (Using PMT function of excel with Rate =0.3333%, Nper=360, Pv=-175549 m New Loan Outstanding for 5 years OLD Loan Balance after 5 years: Future Value of Monthly Payments after 5 years(60 months) $83,661 (Using FV function of excel with Rate =0.5%, Nper=60, Pmt=-1199.10) H25 - x fc A B C D K L M N O 5.19.10 =FV(0.5%,60,-H17) E F Pmt Rate Nper FV FV1 FV1-FV Monthly Payment on old Loan Monthly interest rate =(6/12)% Number of months of payments made=8*12 Future Value of Past Payments Today Future Value of Old Loan Today Current Loan Balance on Old Loan $1,199.10 0.5% 96 $147,284 $322,829 $175,545 (Using FV function of excel with Rate =0.5%, Nper=96, Pmt=-1199.10) (Using FV function of excel with Rate =0.5%, Nper96, Pv=-200000) Pv Rate Nper PMT Monthly Payment on NEW Loan New Loan amount Monthly interest rate =(4/12)% Number of months of amortization=30*12 Monthly Payment on old Loan $175,545 0.3333% 360 $838 (Using PMT function of excel with Rate =0.3333%, Nper=360, Pv=-175545) New Loan Outstanding for 5 years OLD Loan Balance after 5 years: Future Value of Monthly Payments after 5 years(60 months) Future Value of Old Loan After 5 years $83,661 $236,784 | (Using FV function of excel with Rate =0.5%, Nper=60, Pmt=-1199.10) (Using FV function of excel with Rate =0.5%, Nper96, Pv=-175545) Clipboard Font Alignment Number Styles Cells Editing H28 =FV(H18,60,-H20) x E D fc F Rate Nper PMT Monthly interest rate =(4/12)% Number of months of amortization=30*12 Monthly Payment on old Loan H I J K L M N O 0.3333% 360 $838.08 (Using PMT function of excel with Rate =0.3333%, Nper=360, Pv=-175545) New Loan Outstanding for 5 years OLD Loan Balance after 5 years: FV2 Future Value of Monthly Payments after 5 years(60 months) FV3 Future Value of Old Loan After 5 years FV3-FV4 Terminal Payment on old loan at the end of 5 years $83,661 $236,784 $153,122 (Using FV function of excel with Rate =0.5%, Nper=60, Pmt=-1199.10) (Using FV function of excel with Rate =0.5%, Nper=96, Pv=-175545) FV5 Future Value of NEW Loan Monthly paymentsAfter 5 years $55,564 | (Using FV function of excel with Rate =0.3333%, Nper=60, Pmt=-838.08) H6 X fi =PMT(H4, H5,-H3) A B C D E H I J K L M N O Pv Rate Nper PMT Old Loan amount Monthly interest rate =(6/12)% Number of months of amortization=30*12 Monthly Payment on old Loan $200,000 0.5% 360 $1,199.10 (Using PMT function of excel with Rate =0.5%, Nper=360, Pv=-200000) H20 X V f : B =PMT(H18,419,-417) D E F A C H I J K L M N O a Pv Rate Payment on Old Loan Old Loan amount Monthly interest rate =(6/12)% Number of months of amortization=30*12 Monthly Payment on old Loan $200,000 0.5% 360 $1,199.10 Nper PMT (Using PMT function of excel with Rate =0.5%, Nper=360, PvP-200000) $1,199.10 0.5% b Pmt Rate Nper FV FV1 FV1-FV Current Loan Balance on Old Loan Monthly Payment on old Loan Monthly interest rate =(6/12)% Number of months of payments made=8*12 Future Value of Past Payments Today Future Value of Old Loan Today Current Loan Balance on Old Loan 96 $147,284 $322,829 $175,545 (Using FV function of excel with Rate =0.5%, Nper=96, Pmt=-1199.10) (Using FV function of excel with Rate =0.5%, Nper=96, Pv=-200000) Pv Rate Monthly Payment on NEW Loan New Loan amount Monthly interest rate =(4/12)% Number of months of amortization=30*12 Monthly Payment on old Loan $175,545 0.3333% 360 $838 (Using PMT function of excel with Rate =0.3333%, Nper=360, Pv=-175545) Nper PMT H33 : for =PV(H18,60,-H32,H31) X F E H I J K L M N O Pv Rate Nper PMT Monthly Payment on NEW Loan New Loan amount Monthly interest rate =(4/12)% Number of months of amortization=30*12 Monthly Payment on old Loan $175,545 0.3333% 360 $838.08 (Using PMT function of excel with Rate =0.3333%, Nper=360, Pv=-175545) d New Loan Outstanding for 5 years OLD Loan Balance after 5 years: FV2 Future Value of Monthly Payments after 5 years(60 months) FV3 Future Value of Old Loan After 5 years FV3-FV4 Terminal Payment on old loan at the end of 5 years $83,661 $236,784 $153,122 (Using FV function of excel with Rate =0.5%, Nper=60, Pmt=-1199.10) (Using FV function of excel with Rate =0.5%, Nper=60, Pv=-175545) (Using FV function of excel with Rate =0.3333%, Nper=60, Pmt=-838.08) (Using FV function of excel with Rate =0.3333%, Nper=60, Pv=-175545) FV5 Future Value of NEW Loan Monthly paymentsAfter 5 years FV6 Future Value of NEW Loan After 5 years FV6-FV5 Terminal Payment on NEW loan at the end of 5 years Fv Additional Terminal Payment for New Loan=158776-153122 Pmt Savings in monthly payment for new loan=1199.10-838.08 PV Present Value of total Savings for 60 months $55,564 $214,339 $158,776 $5,654 $361.02 $14,973 (Using PV function of excel with Rate =0.3333%, Nper=60, Pmt=361.02, Fv=5654)