Question

I'm confused on this assignment, I'm not certain on what formulas on excel I need to use for this?

Part 1: Loan Affordability Analysis Create a monthly payment table with changing interest rate and loan amount and allow users to change the term and affordable payment as below and use the conditional formatting to highlight the area of rate/loan that is affordable (less than or equal to the affordable payment) with green color and area that is not affordable with red color. A B С D E F G 1 Term 2 Affordable payment 3 30 1600 Monthly Payment Table 4 5 Rate/Loan 6 7 8 9 10 $200,000 $250,000 $300,000 $350,000 $400,000 $450,000 $50 $843.21 $1,054.01 $1,264.81 $1,475.61 $1,686.42 $1,897.22 $2,1 $870.41 $1,088.02 $1,305.62 $1,523.22 $1,740.83 $1,958.43 $2,1 $898.09 $1,122.61 $1,347.13 $1,571.66 $1,796.18 $2,020.70 $2,2 $926.23 $1,157.79 $1,389.35 $1,620.90 $1,852.46 $2,084.02 $2,3 $954.83 $1,193.54 $1,432.25 $1,670.95 $1,909.66 $2,148.37 $2,3 $983.88 $1,229.85 $1,475.82 $1,721.79 $1,967.76 $2,213.73 $2,4 $1,013.37 $1,266.71 $1,520.06 $1,773.40 $2,026.74 $2,280.08 $2,5 $1,043.29 $1,304.12 $1,564.94 $1,825.77 $2,086.59 $2,347.41 $2,6 $1,073.64 $1,342.05 $1,610.46 $1,878.88 $2,147.29 $2,415.70 $2,6 $1,104.41 $1,380.51 $1,656.61 $1,932.71 $2,208.81 $2,484.92 $2,7 $1,135.58 $1,419.47 $1,703.37 $1,987.26 $2,271.16 $2,555.05 $2,8 3.00% 3.25% 3.50% 3.75% 4.00% 4.25% 4.50% 4.75% 5.00% 5.25% 5.50% 11 12 13 14 15 16

Answer 1

To solve the above in excel, PMT formula needs to be used. For example, Interest rate with 3% and Loan amount of $200,000, the monthly payment will be calculated as:
= PMT (rate of interest (monthly rate) (rate), number of periods (monthly) (nper), -loan amount (pv)
= PMT(0.03/12,30 *12,-200000)
=$843.21

The formula to use in excel is attached which can be applied by dragging the cells across rows and column.

For conditional formatting also, refer the formula to use as below:

B6 X =PMT($A6/12,$B$1*12,-B$5) A B с D E F G H 30 1600 Monthly payment table $500,000 1 Term 2 Affordable payment 3 4 5 Rate/loan 6 3.00%) 7 3.25% 8 3.50% 9 3.75% 10 4.00% 11 4.25% 12 4.50% 13 4.75% 14 5.00% 15 5.25% 16 5.50% 17 5.75% 18 6.00% 10 $200,000 $843.21 $870.41 $898.09 $926.23 $954.83 $983.88 $1,013.37 $1,043.29 $1,073.64 $1,104.41 $1,135.58 $1,167.15 $1,199.10 $250,000 $1,054.01 $1,088.02 $1,122.61 $1,157.79 $1,193.54 $1,229.85 $1,266.71 $1,304.12 $1,342.05 $1,380.51 $1,419.47 $1,458.93 $1,498.88 $300,000 $1,264.81 $1,305.62 $1,347.13 $1,389.35 $1,432.25 $1,475.82 $1,520.06 $1,564.94 $1,610.46 $1,656.61 $1,703.37 $1,750.72 $1,798.65 $350,000 $1,475.61 $1,523.22 $1,571.66 $1,620.90 $1,670.95 $1,721.79 $1,773.40 $1,825.77 $1,878.88 $1,932.71 $1,987.26 $2,042.50 $2,098.43 $400,000 $1,686.42 $1,740.83 $1,796.18 $1,852.46 $1,909.66 $1,967.76 $2,026.74 $2,086.59 $2,147.29 $2,208.81 $2,271.16 $2,334.29 $2,398.20 $450,000 $1,897.22 $1,958.43 $2,020.70 $2,084.02 $2,148.37 $2,213.73 $2,280.08 $2,347.41 $2,415.70 $2,484.92 $2,555.05 $2,626.08 $2,697.98 $2,108.02 $2,176.03 $2,245.22 $2,315.58 $2,387.08 $2,459.70 $2,533.43 $2,608.24 $2,684.11 $2,761.02 $2,838.95 $2,917.86 $2,997.75 Term Affordable payment 130 1600 Monthly payment table Rate/loan 0.03 0.0325 0.035 0.0375 0.04 0.0425 0.045 0.0475 0.05 0.0525 0.055 0.0575 0.06 200000 I=PMT($A6/12,$B$1*12,-B$5) =PMT($A7/12,$B$1*12,-B$5) =PMT($A8/12,$B$1*12,-B$5) =PMT($A9/12,$B$1*12,-B$5) =PMT($A10/12,$B$1*12,-B$5) =PMT($A11/12,$B$1*12,-B$5) =PMT($A12/12,$B$1*12,-B$5) =PMT($A13/12,$B$1*12,-B$5) =PMT($A14/12,$B$1*12,-B$5) =PMT($A15/12,$B$1*12,-B$5) =PMT($A16/12, $B$1*12,-B$5) =PMT($A17/12,$B$1*12,-B$5) =PMT($A18/12,$B$1*12,-B$5) (250000 I=PMT($A6/12,$B$1*12,-C$5) =PMT($A7/12,$B$1*12,-C$5) =PMT($A8/12,$B$1*12,-C$5) =PMT($A9/12,$B$1*12,-C$5) =PMT($A10/12,$B$1*12,-C$5) =PMT($A11/12,$B$1*12,-C$5) =PMT($A12/12,$B$1*12,-C$5) =PMT($A13/12,$B$1*12,-C$5) =PMT($A14/12,$B$1*12,-C$5) =PMT($A15/12, $B$1*12,-C$5) =PMT($A16/12,$B$1*12,-C$5) =PMT($A17/12,$B$1*12,-C$5) =PMT($A18/12,$B$1*12,-C$5) 300000 =PMT($A6/12,$B$1*12,-D$5) =PMT($A7/12,$B$1*12,-D$5) =PMT($A8/12,$B$1*12,-D$5) =PMT($A9/12,$B$1*12,-D$5) =PMT($A10/12,$B$1*12,-D$5) =PMT($A11/12,$B$1*12,-D$5) =PMT($A12/12,$B$1*12,-D$5) =PMT($A13/12,$B$1*12,-D$5) =PMT($A14/12,$B$1*12,-D$5) =PMT($A15/12,$B$1*12,-D$5) =PMT($A16/12, $B$1*12,-D$5) =PMT($A17/12,$B$1*12,-D$5) =PMT($A18/12,$B$1*12,-D$5) 350000 =PMT($A6/12,$B$1*12,-E$5) =PMT($A7/12,$B$1*12,-E$5) =PMT($A8/12,$B$1*12,-E$5) =PMT($A9/12,$B$1*12,-E$5) =PMT($A10/12,$B$1*12,-E$5) =PMT($A11/12,$B$1*12,-E$5) =PMT($A12/12,$B$1*12,-E$5) =PMT($A13/12,$B$1*12,-E$5) =PMT($A14/12,$B$1*12,-E$5) =PMT($A15/12,$B$1*12,-E$5) =PMT($A16/12,$B$1*12,-E$5) =PMT($A17/12,$B$1*12,-E$5) =PMT($A18/12,$B$1*12,-E$5) Term Affordable payment 30 1600 Monthly payment table Rate/loan $200,000 $250,000 $300,000 $350,000 $400,000 $450,000 $500,000 3.00% $843.21 $1,054.01 $1,264.81 $1,475.61 $1,686.42 $1,897.22 $2,108.02 3.25% $870.41 $1,088.02 $1,305.62 $1,523.22 $1,740.83 $1,958.43 $2,176.03 3.50% $898.09 $1,122.61 $1,347.13 $1,571.66 $1,796.18 $2,020.70 $2,245.22 3.75% $926.23 $1,157.79 $1,389.35 $1,620.90 $1,852.46 $2,084.02 $2,315.58 4.004 $954 22 $1 192 54 51 432 25 51 670 95 $1 909 66 52 149 27 52 287 ng Conditional Formatting Rules Manager ? Show formatting rules for: Current Selection New Rule... Edit Rule... X Delete Rule Rule (applied in order shown) Format Applies to Stop If True Cell Value < $B$2 AaBbCcYyZz = $B$6:SH$18 Cell Value > SBS2 AaBb CcYyZz =SBS6:SH$18 She OK Close Apply