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