Creating a Loan Calculator in Excel

1. Create the following columns: Payment Number, Payment Date, Beginning Balance, Payment, Interest, Principal, and Ending Balance.

1. In the Payment Number column, enter the numbers 1 through the total number of payments you want to calculate.
2. In the Payment Date column, enter the first payment date. Then, in the cell below it, enter the formula "=EDATE(A2,1)" and drag it down to fill in the rest of the payment dates.
3. In the Beginning Balance column, enter the loan amount.
4. In the Payment column, enter the formula "=PMT(rate/12,360,beginning balance)" where "rate" is the annual interest rate and "beginning balance" is the balance at the beginning of the payment period. This will calculate the payment amount based on a 360-day year.
5. In the Interest column, enter the formula "=beginning balance*rate/360" where "rate" is the annual interest rate. This will calculate the interest for the payment period based on a 360-day year.
6. In the Principal column, enter the formula "=payment-interest" to calculate the principal payment for the payment period.
7. In the Ending Balance column, enter the formula "=beginning balance-principal" to calculate the balance at the end of the payment period.
8. Drag the formulas down to fill in the rest of the rows.
9. To make the amortization schedule easier to read, you can format the cells as currency and adjust the decimal places as needed.

That's it! You now have a loan calculator that will calculate and generate an amortization schedule based upon a 360-day year. Let me know if you have any questions or if there's anything else I can help you with.