How do I get C and D using excel?

How

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 repay 5 the old loan and the cost of refinancing. 7 A - How much would you still owe on the loan after 2 years? Hint: Utilize the CUMPRINC formula here! -3870.34142 9 Balance of old loan today $156,129.67 10 11 B - Calculate the amount of the new loan and monthly payments of each loan: 12 Total Loan Monthly Annual Rate Life (In Years) Periods Paid Cost Amount Payments 14 Initial Loan 6.25% 30 $ 160,000.00 $985.15 24 Refinancing 5.50% 30 $156,129.67 $886. 490 | $ 5,000 13 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 27 28 Old Loan New Loan 29 Loan Balance at Move PV at move (consider balance and monthly payments) 31 NPV of Refinancing 32 Refinance? 30 month 34 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 repay 5 the old loan and the cost of refinancing. 7 A - How much would you still owe on the loan after 2 years? Hint: Utilize the CUMPRINC formula here! -3870.34142 9 Balance of old loan today $156,129.67 10 11 B - Calculate the amount of the new loan and monthly payments of each loan: 12 Total Loan Monthly Annual Rate Life (In Years) Periods Paid Cost Amount Payments 14 Initial Loan 6.25% 30 $ 160,000.00 $985.15 24 Refinancing 5.50% 30 $156,129.67 $886. 490 | $ 5,000 13 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 27 28 Old Loan New Loan 29 Loan Balance at Move PV at move (consider balance and monthly payments) 31 NPV of Refinancing 32 Refinance? 30 month 34