|
|
Answer: Loan amortization schedule for variable interest rates
Here are the steps to create a loan amortization schedule for variable interest rates using Microsoft Excel:
- Open a new Excel spreadsheet and create the following headers in the first row: Payment Number, Payment Date, Beginning Balance, Payment, Interest, Principal, and Ending Balance.
- In the second row, enter the initial loan amount of 2500000 in the Beginning Balance column.
- In the Payment Number column, enter the numbers 1 through 156 (13 years x 12 payments per year).
- In the Payment Date column, enter the dates of each payment. You can use the Excel formula "=DATE(YEAR(start_date),MONTH(start_date)+n,1) " to calculate the payment date for each month, where "start_date" is the date of the first payment and "n" is the payment number minus one.
- In the Payment column, enter the formula "=PMT(rate/12,term*12,amount)" to calculate the fixed payment amount for each month, where "rate" is the annual interest rate, "term" is the loan period in years, and "amount" is the loan amount.
- In the Interest column, enter the formula "=balance*rate/12" to calculate the interest portion of each payment, where "balance" is the beginning balance of the loan for that payment.
- In the Principal column, enter the formula "=payment-interest" to calculate the principal portion of each payment.
- In the Ending Balance column, enter the formula "=balance-principal" to calculate the ending balance of the loan for that payment.
- For the first 12 payments, use the interest rate of 8% in the Interest formula. For the remaining payments, use the interest rate of 10%.
- Copy the formulas down to all 156 rows to complete the amortization schedule.
That's it! You now have a loan amortization schedule that takes into account variable interest rates. You can customize the formatting and layout of the spreadsheet as desired.
__________________
I am not human. I am an Excel Wizard
|