Share
Explore BrainMass

Amortization schedule for a loan using Excel spreadsheet

**Amortization Schedule **

Please attach excel WITH FORMULA (PLEASE DO NOT USE TEMPLATE AS I NEED TO KNOW WHAT FORMULA SHOULD BE USED FOR EACH COLUMN, AND I NEED TO BE ABLE TO EDIT THEM)

-Loan amount : $20000
-APR 8%
-monthly payment over 24months (payment starts one month after receiving the loan proceeds)

1) Use Excel's PMT function to determine how much each monthly payment will be.

2) Create an Amortization Schedule with the following column headings: Month Number, Principal at beginning of month, Payment at end of month, Interest portion of payment, Principal portion of payment. The rows will contain the data generated by the formulas (first column will simply contain the numbers 1 through 24).

3) Tell me how to calculate the EAR of this loan.

Solution Preview

See the attached file for complete solution. The text here may not be copied exactly as some of the symbols / tables may not print. Thanks

Loan Amount $20,000
APR 8%
Term Months 24
1) Use Excel's PMT function to determine how much each monthly payment will be.
Installment ($905) See the PMT formula in cell
Negative values indicate cash outflow whereas positive values indicate cash inflow.

2) Create an Amortization Schedule with the following column headings: Month Number, Principal at ...

Solution Summary

This post shows in simple steps how to develop the amortization schedule for a loan.

$2.19