Find all values for the light blue boxes using excel formulas.
C F G H Question 2 - Mortgage Refinancing II Suppose you purchased you first house 2 years ago and took out a mortgage for $160,000 with a 6.25% interest rate. The mortgage is a 30 year loan with monthly payments. Today you can refinance the loan at a 5.50% interest rate for a fixed fee of $5,000. Assume that you would only refinance enough to repay the old loan and the cost of refinancing. A - How much would you still owe on the loan after 2 years? Hint: Utilize the CUMPRINC formula here! - Balance of old loan today (53,870 34) D 1 B - Calculate the amount of the new loan and monthly payments of each loan: 2 Annual Rate Total Loan Periods Life (In Years) Monthly Cost 3 Amount Payments Paid 4 Initial Loan 6.25% 30 $ 160,000.00 ($985.15) 24 5 Refinancing 5.50% 30 ($886.49) 0 S 5,000 26 17 C - Would you refinance today? 18 Old Loan New Loan 19 PV (of monthly payments) 20 NPV of Refinancing 21 Refinance? 22 23 D - Now, consider you are at the end of year 2, as is the case above, and you expect to move in 4 years time, would you want to refinance? 24 25 Year Months 26 Time Till Move 48 27 28 Old Loan New Loan 29 Loan Balance at Move PV at move (consider balance and 30 monthly payments) 31 NPV of Refinancing 32 Refinance? 33 34 35 36 4 37 38 39 40 Start Mortgage Refin. Mortgage Refin. Il Amortization Conditional & Lookup Functions + Tell me to V ulil III lib % Homework 1 Part 2.JD Home Insert Draw Page Layout Formulas Data Review View X Times New Roman 12 ' Wrap Text General Paste BIU A Merge & Center $ % , A1 Xvfx Question 2 - Mortgage Refinancing || A D H 1 Question 2 - Mortgage Refinancing II 3 Suppose you purchased you first house 2 years ago and took out a mortgage for $160,000 with a 6.25% interest rate. The mortgage is a 30 year loan with 4 monthly payments. Today you can refinance the loan at a 5.50% interest rate for a fixed fee of $5,000. Assume that you would only refinance enough to 5 repay the old loan and the cost of refinancing. 6 7 A - How much would you still owe on the loan after 2 years? 8 Hint: Utilize the CUMPRINC formula here! 9 Balance of old loan today (53,870.34) 10 11 B-Calculate the amount of the new loan and monthly payments of each loan: 12 Total Loan Monthly Periods Annual Rate Life (In Years) Cost 13 Amount Payments Paid 14 Initial Loan 6.25% 30 $ 160,000.00 (5985.15) 24 5.50% 30 15 Refinancing (5886.49) 0 $ 5,000 16 17 C - Would you refinance today? 18 New Loan Old Loan 19 PV (of monthly payments) 20 NPV of Refinancing 21 Refinance? 22 23 D. Now, consider you are at the end of year 2, as is the case above, and you expect to move in 4 years time, would you want to refinance? 24 Year Months 25 48 26 Time Till Move 22 - Calculate the amount of the new loan and monthly payments of each loan: Annual Rate Cost Total Loan Amount $ 160,000.00 Life (In Years) 30 30 Initial Loan Refinancing Periods Paid 24 0 Monthly Payments (5985.15) (5886.49) 6.25% 5.50% $ 5,000 C - Would you refinance today? Old Loan New Loan PV (of monthly payments) NPV of Refinancing 1 Refinance? 2 3 D - Now, consider you are at the end of year 2, as is the case above, and you expect to move in 4 years time, would you want to refinance? 24 25 Year Months 26 Time Til Move 4 48 27 28 Old Loan New Loan 29 Loan Balance at Move PV at move (consider balance and 30 monthly payments) 31 NPV of Refinancing 32 Refinance? 33 34 35 36 37 38 39 C F G H Question 2 - Mortgage Refinancing II Suppose you purchased you first house 2 years ago and took out a mortgage for $160,000 with a 6.25% interest rate. The mortgage is a 30 year loan with monthly payments. Today you can refinance the loan at a 5.50% interest rate for a fixed fee of $5,000. Assume that you would only refinance enough to repay the old loan and the cost of refinancing. A - How much would you still owe on the loan after 2 years? Hint: Utilize the CUMPRINC formula here! - Balance of old loan today (53,870 34) D 1 B - Calculate the amount of the new loan and monthly payments of each loan: 2 Annual Rate Total Loan Periods Life (In Years) Monthly Cost 3 Amount Payments Paid 4 Initial Loan 6.25% 30 $ 160,000.00 ($985.15) 24 5 Refinancing 5.50% 30 ($886.49) 0 S 5,000 26 17 C - Would you refinance today? 18 Old Loan New Loan 19 PV (of monthly payments) 20 NPV of Refinancing 21 Refinance? 22 23 D - Now, consider you are at the end of year 2, as is the case above, and you expect to move in 4 years time, would you want to refinance? 24 25 Year Months 26 Time Till Move 48 27 28 Old Loan New Loan 29 Loan Balance at Move PV at move (consider balance and 30 monthly payments) 31 NPV of Refinancing 32 Refinance? 33 34 35 36 4 37 38 39 40 Start Mortgage Refin. Mortgage Refin. Il Amortization Conditional & Lookup Functions + Tell me to V ulil III lib % Homework 1 Part 2.JD Home Insert Draw Page Layout Formulas Data Review View X Times New Roman 12 ' Wrap Text General Paste BIU A Merge & Center $ % , A1 Xvfx Question 2 - Mortgage Refinancing || A D H 1 Question 2 - Mortgage Refinancing II 3 Suppose you purchased you first house 2 years ago and took out a mortgage for $160,000 with a 6.25% interest rate. The mortgage is a 30 year loan with 4 monthly payments. Today you can refinance the loan at a 5.50% interest rate for a fixed fee of $5,000. Assume that you would only refinance enough to 5 repay the old loan and the cost of refinancing. 6 7 A - How much would you still owe on the loan after 2 years? 8 Hint: Utilize the CUMPRINC formula here! 9 Balance of old loan today (53,870.34) 10 11 B-Calculate the amount of the new loan and monthly payments of each loan: 12 Total Loan Monthly Periods Annual Rate Life (In Years) Cost 13 Amount Payments Paid 14 Initial Loan 6.25% 30 $ 160,000.00 (5985.15) 24 5.50% 30 15 Refinancing (5886.49) 0 $ 5,000 16 17 C - Would you refinance today? 18 New Loan Old Loan 19 PV (of monthly payments) 20 NPV of Refinancing 21 Refinance? 22 23 D. Now, consider you are at the end of year 2, as is the case above, and you expect to move in 4 years time, would you want to refinance? 24 Year Months 25 48 26 Time Till Move 22 - Calculate the amount of the new loan and monthly payments of each loan: Annual Rate Cost Total Loan Amount $ 160,000.00 Life (In Years) 30 30 Initial Loan Refinancing Periods Paid 24 0 Monthly Payments (5985.15) (5886.49) 6.25% 5.50% $ 5,000 C - Would you refinance today? Old Loan New Loan PV (of monthly payments) NPV of Refinancing 1 Refinance? 2 3 D - Now, consider you are at the end of year 2, as is the case above, and you expect to move in 4 years time, would you want to refinance? 24 25 Year Months 26 Time Til Move 4 48 27 28 Old Loan New Loan 29 Loan Balance at Move PV at move (consider balance and 30 monthly payments) 31 NPV of Refinancing 32 Refinance? 33 34 35 36 37 38 39
