i have already answered option one and two on excel. however i am unsure how to set up excel formulas to solve for option 2 part 2 and the very bottom question

Lo A. % . En conditional Formatting o Conditional Formatting ting Format as Table 2 Cell Styles Paste Font Alignment Number Cells Editing 135 x fx 1 Time Value of Money Case 2 Option 1 3 Negotiated Price 4 Down Payment 5 Mortgage Life 6 Mortgage Rate 175000 Monthly rate 20000 total no. of payments amount financed 0.04925 PV annuity factor Monthly Payment 30 0.00410417 360 155000 187.882583 824.983336 Total Payments Total Principal Total Interest SMS 296994.001 155000 141994.001 First Payment Interest in First payment Principal in First payment Unpaid principal after 10 years 824.983336 636.145833 188.837502 15826.4856 17 Option 2 8 Negotiated Price 9 Down Payment O Mortgage Life 1 Mortgage Rate 95000 Monthly rate 20000 total no. of payments amount financed 0.04775 PV annuity factor Monthly Payment 0.00397917 180 75000 128.349511 584.341921 Total Payments Total Principal Total Interest 105181.546 75000 30181.5458 First Payment Interest in First payment Principal in First payment Unpaid principal after 10 years 584.341921 298.4375 285.904421 2887.82478 MSD0Nmmmm 3 Option 2. part 2 240.641415 Monthly investment difference Investment maturity value after 15 years Total investment payments made total investment interest earned net of investment interest earned & mortgage interest paid Two different house purchases are being considered 1) a larger home financed with a longer mortgage and 2) a smaller home with a shorter mortgage. Set up an Excel template to determine each of the items on the right below. Option 1 Monthly Payment Negotiated Price 175,000 Total Interest Down Payment 20,000 Interest in 1st Payment Mortgage Life 30 years Principal in 1st Payment Mortgage Rate 4.925% Unpaid Principal after 10 years Option 2 Monthly Payment Negotiated Price 95,000 Total Interest Down Payment 20,000 Interest in 1st Payment Mortgage Life 15 years Principal in 1st Payment Mortgage Rate 4.775% Unpaid Principal after 10 years Option 2 has a second part. The smaller home with a shorter mortgage has a smaller monthly payment. So the buyer will take the difference in the two monthly payments and invest it every month earning 7.5% for the first 15 years. In other words, the homeowner has a monthly budget of $825 for housing. In the option of buying the larger home, the budget is used up with just the monthly mortgage payment. In the option of buying the smaller home, there is room to make the smaller monthly payment and invest the unused budget amount Add the Excel formula work necessary to determine the following: Monthly Investment (difference in the two payments) Investment Maturity Value after 15 years Total Investment Payments Made Total Investment Interest Earned Net of investment interest earned & mortgage interest paid If you sold the smaller home after 15 years and purchased the larger home, how much would you need to finance over the second fifteen years given the following market values at the end of the first 15 years? Value of larger home 255,000 Value of smaller home 137.500