I need the formulas to calculate Normal Payoff Date, Actual Number of Payments, Actual Payoff Date, Total of Extra Payments, and Total of All Interest Payments in the Summary section. The amortization table cannot display all values for a loan that is longer than 20 years and display negative values for a loan that is less than 20 years. And the formula for the Extra Payment field.

original question:

You have recently taken a position in a bank. Your first client would like assistance planning for a loan. You want to set up a detailed loan amortization table. Open the attached Excel file LoanAmortization.xlsx. This file contains a worksheet with a loan parameters area (i.e., input area), a summary area, and amortization table column labels. You must use this template to complete a detailed loan amortization table and the summary area. Any other format of your amortization schedule will not be acceptable. You want to hide zeros from displaying if your client takes out a shorter-term loan or pay it off early. However, you must keep formulas in place for a traditional 30-year loan. In addition, you will notice overpayments on the last payment if you pay extra toward the principal each month. Add a header in the worksheet and place your name in the header.

Amortization Schedule $2,923.71 240 Loan Parameters $ 400,000 Principal 6.25% Annual Interest Rate 3/1/2015 Date of First Payment 20 Term of Loan (Years) 12 Number of Payments Per Year Extra Payment Every Period Loan Summary Information Monthly Payment (P&I Only) Scheduled Number of Payments Normal Payoff Date Actual Number of Payments Actual Payoff Date Total of Extra Payments Total of All Interest Payments Payment Number Interest Principal WN- - Beginning Date Balance 3/1/2015 $ 400.000 4/1/2015 $ 399,159.62 5/1/2015 $ 398,314.86 6/1/2015 $ 397,465.71 | 7/1/2015 $ 396.612.13 8/1/2015 $ 395,754.10 9/1/2015 $ 394.891.61 10/1/2015 $ 394,024.63 11/1/2015 $ 393,153.12 12/1/2015 $ 392,277.08 Regular Payment 2923.71281 $ 2923.71281 $ 2923.71281 $ 2923.71281 $ 2923.71281 $ 2923.71281 $ 2923.71281 $ 2923.71281 $ 2923.71281 $ 2923.71281 $ 2.083.33 $ 2,078.96 $ 2,074.56 $ 2,070.13 $ 2,065.69 $ 2,061.22 $ 2.056.73 $ 2,052.21 $ 2,047.67 $ 2,043.11 $ 840.38 844.76 849.16 853.58 858.02 862.49 866.99 871.50 876.04 880.60 Ending Extra Payment Balance 0 $ 399,159.62 $ 398,314.86 $ 397,465.71 $ 396,612.13 $ 395,754.10 $ 394,891.61 $ 394.024.63 $ 393,153.12 $ 392,277.08 $ 391,396.48 00 O 10