Please help with the nested if function for cell B10. I have attached everything you need to help. I also zoomed in on the spreadsheet to make it more visable so thats why its split up.

Then, on your own, please add one more input (as highlighted in yellow) called prepayment period (which you can plug in any number between 1 and 360), and solve for the prepayment-adjusted effective borrowing cost, both before and after income-tax (as two new outputs). You should end up having something like this: your extension on prepaymernt adjusted effective interest rate is highlighted in yellow. Again, your spreadsheet shall be self-adaptive. So when we change the prepayment to another number (say, 84 in the below set up), it shall deliver the correct effective interest rate for the above loan when prepaid in seven years (e.g., 3.52% pre-tax, and 2.81% post tax). ,00. 00 1125 Monty Payment 148 1528 1% ees/Discount Poir Hints for excel: you need to use absolute reference (S reference), and use nested. If (to check out whether some conditions are met). If you are not familiar with these features, pay attention to my lecture or google them. A useful web demo on nested if can be found at http://www.excel-easy.com/examplesested-if.html 10 27 44-61 77 34-10 25 41 56-71 86 01 15 30 44 57 71 4 7 0 23 35 48 60 71 0-40-3232 696969 $ $ 69 69 $ $ $ 10 25 41 57 75 34 14 35 58 81 05 31 58 85 14 44 75 07 41 75 48 86 25 65 06 2. 17-5348-26 51 5948 2. 77 1 5 0 49 IR PP E al al 9990 er or en re te ep ep gelda 532 05 7682 05 81 57 32 05 7 4 18 87 55 22 87 51 15 7 3 9 56 4951 5555555 5555 54 45 5 5 5 5 5 5 5 5 5 5 5 64 00 5% 12-15 00-20 0%-120 0 90 65 24 7 91 9 3 8000 73 15 30 16 2 7 9 8 7 62 14 29 04 9 33 8-7, 5-3 $ 691 $ $ $. 123456789 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 e ea ar al 123456 783 10 11 12 13-14 15 16 17-13 20 2 22 3 24 25 2 2 2203 2 3 3 3 3 3 3 3 40 Before-Tax Effeci After-Tax Effective F BT TakEffectivePaym EndingBalance(OSB uma Principa te mula e tereR -$357,700 00 182542 $359,160.00 $361,81165$159210 $360,21124 $358,606.67 $356,997.91 $355 384 97 $35376783S11,232.17 $352,146.48 $350,520.30 $348,89109 $347,257.04 $345,618 73 $343,976.15 $363.407.90 $950.52 $1,896.90 $2,839.11 ($2,542.62) $3,777.16 $2,542.62 $2.353.35 1- $4.788.76 $6,393.33 $8,002.09 $3.615.03 $2,54262) $2,54262 $2.35585) $5,640.72 $6,566.20 $7.48747 $8,404.52 $2.357 53) $2,356.69) ($2,357.53) $1285352 $14.479.10 $2,542.62 359.2352 $2,542.62 $2,542.62 $2,542.62 $2,542.62) $2,542 62) $2,542.62) $2,542.62 ($2,54262) 0.91 $17,742.96 ($2.36176C $2.36261) $2.36347) $21,023.85 $22,670.70 $24,32184 $25,977.28 $27,637.03 $29,301.11 $30,969.52 $32,642.27 $11,130.22 $12,030.27 $12,926.04 $13,817.52 $14,704.70 $15,587.57 $16,466.12 $17,340.34 $342.329.30 $340,678.16 $2365.19) $339,022.72 $337.36297 $335.698.89 $334,030.48 $332,357.73 $330,680.62 $2.365.19) ($2,366.91 ($2.368.65) $2,369.52) ($2.370.39) ($2,37127) ( $2.54262). $19,075.73 $19,936.87 $20,793.64 $21,646.02 $22,494.00 $23,337.56 $18,210.21 ($2,542.62) ($2542.62) $2.54262) ($2,542 62) ($2,54262) ($2,54262) $2,54262 12.37039) $328,939.14 $327,31329 823 $237303) 2.374.00] $36,000.86 $37,686.71 $39,376.96 $4107161 $42,770.67 $44.474.15 376 $325,623.04 $323,928.39 $322,22933 $320.525.85 ($2,37303) $2,373.91 124.176.70- 2.542.6 0 Before-Tak Effeci After-Tax Effective F BT: Prepayment AT Prep $359,160.00 $2,35252) $2,353.35) $2,354.18) $2,355.01 $2,355.85 $357.700.00 $359,160.00 $254262($2,35252 $2,542.62] ($2.353.35 $2,542.62) ($2,354.18 $2,542.62 $2,54262)($2,355.85) ($2,542.62) I$2,356.69 ($2,54262 ($2.357 53 ($2,542.62 ($254262$2,359.21) $2,54262)($2.360.06) ($2,542.62) ($2.36091 $2,542.62) ($254262) ($2,36261) ($2542.62] -6236347 $2.54262) ($2,364.33) ($2,54262)($2.365.19) ($2,54262)($2366.05) ($2,542 621_ ($2.366 911 $2,542.62)($2,367.78) $2,542.62)($2,368.65) $2,54262)($2,369.52) ($2,542.62)--($2,370.39 $2.54262)($2,37127) ($254262) ($2.37215) $2.54262)($2,373.03) ($2,542.62) ($2,373.91) ($2.542.621$2.374.801 $357,700.00 $2,542.62) ($2,542.62) ($2,542621 $2.54262 $2.54262 $2,355.01 () $2,.356.69) $2,357.53) ($2,358.37) $2,359.21 I$2,360.06 ($2,54262) ($2,542.62) ] ($2358.37 ($2,542.62) $2,542.62 ($2,542.62) $2,54262 .($2.54262/--(t236091 -($2.542.62) $2,361.76) ($2,362.61 $2,363.4 ($2.364.33) ($2.365.19) ($2.366.05) $2,366.91 ($2.367.78) ($2.368.65) $2.36176 $2,542.62) $2.542.62) ($2.542 62) ($2,542.62) $2.54262) ($2,54262) $2,54262) ($2 ($2,542.62)| ($2.369.52) $2.542.62) $2,54262) $2,542.62) ($2,54262($2,3 ($2,542.62) $2.542.6211$2.374 ($2,542621 $2.370.39) ($2,371.27) ($2,372.15) $2.373 91) Then, on your own, please add one more input (as highlighted in yellow) called prepayment period (which you can plug in any number between 1 and 360), and solve for the prepayment-adjusted effective borrowing cost, both before and after income-tax (as two new outputs). You should end up having something like this: your extension on prepaymernt adjusted effective interest rate is highlighted in yellow. Again, your spreadsheet shall be self-adaptive. So when we change the prepayment to another number (say, 84 in the below set up), it shall deliver the correct effective interest rate for the above loan when prepaid in seven years (e.g., 3.52% pre-tax, and 2.81% post tax). ,00. 00 1125 Monty Payment 148 1528 1% ees/Discount Poir Hints for excel: you need to use absolute reference (S reference), and use nested. If (to check out whether some conditions are met). If you are not familiar with these features, pay attention to my lecture or google them. A useful web demo on nested if can be found at http://www.excel-easy.com/examplesested-if.html 10 27 44-61 77 34-10 25 41 56-71 86 01 15 30 44 57 71 4 7 0 23 35 48 60 71 0-40-3232 696969 $ $ 69 69 $ $ $ 10 25 41 57 75 34 14 35 58 81 05 31 58 85 14 44 75 07 41 75 48 86 25 65 06 2. 17-5348-26 51 5948 2. 77 1 5 0 49 IR PP E al al 9990 er or en re te ep ep gelda 532 05 7682 05 81 57 32 05 7 4 18 87 55 22 87 51 15 7 3 9 56 4951 5555555 5555 54 45 5 5 5 5 5 5 5 5 5 5 5 64 00 5% 12-15 00-20 0%-120 0 90 65 24 7 91 9 3 8000 73 15 30 16 2 7 9 8 7 62 14 29 04 9 33 8-7, 5-3 $ 691 $ $ $. 123456789 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 e ea ar al 123456 783 10 11 12 13-14 15 16 17-13 20 2 22 3 24 25 2 2 2203 2 3 3 3 3 3 3 3 40 Before-Tax Effeci After-Tax Effective F BT TakEffectivePaym EndingBalance(OSB uma Principa te mula e tereR -$357,700 00 182542 $359,160.00 $361,81165$159210 $360,21124 $358,606.67 $356,997.91 $355 384 97 $35376783S11,232.17 $352,146.48 $350,520.30 $348,89109 $347,257.04 $345,618 73 $343,976.15 $363.407.90 $950.52 $1,896.90 $2,839.11 ($2,542.62) $3,777.16 $2,542.62 $2.353.35 1- $4.788.76 $6,393.33 $8,002.09 $3.615.03 $2,54262) $2,54262 $2.35585) $5,640.72 $6,566.20 $7.48747 $8,404.52 $2.357 53) $2,356.69) ($2,357.53) $1285352 $14.479.10 $2,542.62 359.2352 $2,542.62 $2,542.62 $2,542.62 $2,542.62) $2,542 62) $2,542.62) $2,542.62 ($2,54262) 0.91 $17,742.96 ($2.36176C $2.36261) $2.36347) $21,023.85 $22,670.70 $24,32184 $25,977.28 $27,637.03 $29,301.11 $30,969.52 $32,642.27 $11,130.22 $12,030.27 $12,926.04 $13,817.52 $14,704.70 $15,587.57 $16,466.12 $17,340.34 $342.329.30 $340,678.16 $2365.19) $339,022.72 $337.36297 $335.698.89 $334,030.48 $332,357.73 $330,680.62 $2.365.19) ($2,366.91 ($2.368.65) $2,369.52) ($2.370.39) ($2,37127) ( $2.54262). $19,075.73 $19,936.87 $20,793.64 $21,646.02 $22,494.00 $23,337.56 $18,210.21 ($2,542.62) ($2542.62) $2.54262) ($2,542 62) ($2,54262) ($2,54262) $2,54262 12.37039) $328,939.14 $327,31329 823 $237303) 2.374.00] $36,000.86 $37,686.71 $39,376.96 $4107161 $42,770.67 $44.474.15 376 $325,623.04 $323,928.39 $322,22933 $320.525.85 ($2,37303) $2,373.91 124.176.70- 2.542.6 0 Before-Tak Effeci After-Tax Effective F BT: Prepayment AT Prep $359,160.00 $2,35252) $2,353.35) $2,354.18) $2,355.01 $2,355.85 $357.700.00 $359,160.00 $254262($2,35252 $2,542.62] ($2.353.35 $2,542.62) ($2,354.18 $2,542.62 $2,54262)($2,355.85) ($2,542.62) I$2,356.69 ($2,54262 ($2.357 53 ($2,542.62 ($254262$2,359.21) $2,54262)($2.360.06) ($2,542.62) ($2.36091 $2,542.62) ($254262) ($2,36261) ($2542.62] -6236347 $2.54262) ($2,364.33) ($2,54262)($2.365.19) ($2,54262)($2366.05) ($2,542 621_ ($2.366 911 $2,542.62)($2,367.78) $2,542.62)($2,368.65) $2,54262)($2,369.52) ($2,542.62)--($2,370.39 $2.54262)($2,37127) ($254262) ($2.37215) $2.54262)($2,373.03) ($2,542.62) ($2,373.91) ($2.542.621$2.374.801 $357,700.00 $2,542.62) ($2,542.62) ($2,542621 $2.54262 $2.54262 $2,355.01 () $2,.356.69) $2,357.53) ($2,358.37) $2,359.21 I$2,360.06 ($2,54262) ($2,542.62) ] ($2358.37 ($2,542.62) $2,542.62 ($2,542.62) $2,54262 .($2.54262/--(t236091 -($2.542.62) $2,361.76) ($2,362.61 $2,363.4 ($2.364.33) ($2.365.19) ($2.366.05) $2,366.91 ($2.367.78) ($2.368.65) $2.36176 $2,542.62) $2.542.62) ($2.542 62) ($2,542.62) $2.54262) ($2,54262) $2,54262) ($2 ($2,542.62)| ($2.369.52) $2.542.62) $2,54262) $2,542.62) ($2,54262($2,3 ($2,542.62) $2.542.6211$2.374 ($2,542621 $2.370.39) ($2,371.27) ($2,372.15) $2.373 91)