60.1K

Verified Solution

Link Copied!

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.

Answer & Explanation Solved by verified expert

See Answer