360-day Year Based Payment & Amortization Calculations?
 

I need to create a loan calculator within Excel that will calculate and
generate amortization schedule based upon a 360-day year (30 days per 12
mos). I found a "How to Create a Loan Amortization Schedule" in product
knowledge base, but have been unable to find how to base these calculations
on a 360-day year (30 days per 12 mos). Ahy help is greatly appreciated.

Answer: 360-day Year Based Payment & Amortization Calculations?
 

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.

360-day Year Based Payment & Amortization Calculations?
 

Use the regular financial functions. The 360-day year (12x30) simply refers
to the fact that each month is assumed to be have same number of days. So as
long as you express your term in months, Excel's financial functions will
properly calculate for you.

Regards,
Fred.

"Kelley from NC" <Kelley from wrote in message
...
I need to create a loan calculator within Excel that will calculate and
generate amortization schedule based upon a 360-day year (30 days per 12
mos). I found a "How to Create a Loan Amortization Schedule" in product
knowledge base, but have been unable to find how to base these
calculations
on a 360-day year (30 days per 12 mos). Ahy help is greatly appreciated.