Question
Amortization Schedule Loan Parameters Loan Summary Information $ 400,000 Principal Monthly Payment (P&I Only) $2,923.71 6.25% Annual Interest Rate Scheduled Number of Payments 240 3/1/2015
Amortization Schedule | |||||||
Loan Parameters | Loan Summary Information | ||||||
$ 400,000 | Principal | Monthly Payment (P&I Only) | $2,923.71 | ||||
6.25% | Annual Interest Rate | Scheduled Number of Payments | 240 | ||||
3/1/2015 | Date of First Payment | Normal Payoff Date | |||||
20 | Term of Loan (Years) | Actual Number of Payments | |||||
12 | Number of Payments Per Year | Actual Payoff Date | |||||
Extra Payment Every Period | Total of Extra Payments | ||||||
Total of All Interest Payments | |||||||
Payment Number | Date | Beginning Balance | Regular Payment | Interest | Principal | Extra Payment | Ending Balance |
1 | 3/1/2015 | $ 400,000 | 2923.71281 | $ 2,083.33 | $ 840.38 | 0 | $ 399,159.62 |
You work in a bank. Your clients would like assistance planning for a loan. You want to set up a detailed loan amortization table. This file contains a worksheet with a loan parameters area (i.e., input area), a summary area, and amortization table column labels. You will use this template to complete a detailed loan amortization table. You want to build in mechanisms to prevent formula errors if input data are missing and 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. To make the amortization table as flexible as possible and to avoid errors, you may need creating several nested IF functions.
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started