Complete Loan Amortization Chart!

[Female]

Hello everyone, I need help with a few questions on this practice sheet. Any help is much appreciated

1. Insert a formula in cell E2 to calculate the loan amount based on the loan parameters in the input area.

2. Insert a function in cell E5 to calculate the monthly payment. Ensure that the function returns a positive value.

3. In cell E6, insert a function to calculate the total interest paid on the loan. Ensure that the function returns a positive value.

4. Use the DATE function to complete the Payment Date column and financial functions for the Interest Paid and Principal Payment columns.

I got stuck on these four questions, but was able to do the rest. Help with formulas and explanations of how you got them would be much appreciated!

Image: http://imgur.com/ceFHPVi

LoanPractice.png

[jewelsharma]

Loan Amount (Cell E2):

Please Login or Register  to view this content.

max function insures that the minimum value is zero.

Monthly Payment (Cell E5):

Please Login or Register  to view this content.

the opposing sign is required for the PMT function to show the direction of money flow. Loan is received (in-the-pocket) and interest is paid out (out-of-pocket).

Total Interest paid (Cell E6):

Please Login or Register  to view this content.

Payment Dates (Cell B11 = Cell B7) the 1st payment date. Thereafter, starting at cell B12, and copy-down:

Please Login or Register  to view this content.

Beginning Balance = Ending Balance at the last installment, the 1st balance is equal to the loan amount (or Cell C11 = Cell E2). Thereafter, starting at Cell C12 and copy-down:

Please Login or Register  to view this content.

Interest Paid = Beginning balance * Interest Rate (starting at Cell D11 and copy-down)

Please Login or Register  to view this content.

Principal Amount = Monthly Installment - Interest Paid in that installment (starting at Cell E11 and copy-down)

Please Login or Register  to view this content.

Ending Balance = Beginning Balance - Principal repaid in that installment (starting at Cell F11 and copy-down)

Please Login or Register  to view this content.

HTH!