A variable-rate amortization table is an important financial tool that helps you understand the repayment of a loan with variable interest rates. Microsoft Excel provides an excellent platform to create a variable-rate amortization table.

Here are the steps you should follow to create a variable-rate amortization table in Excel:

Step 1: Collect information for your loan

To create your variable-rate amortization table, you need to gather some important information about your loan, including the initial loan amount, interest rate, loan term, and payment frequency. You will also need to know the specific formulas used to calculate your loan payments and balance.

Step 2: Open a new Excel spreadsheet

Open a new Excel spreadsheet by clicking on the Microsoft Office button and selecting ‘New’.

Step 3: Enter the loan information

Enter the loan information in the cells of the spreadsheet. Enter the initial loan amount in cell A1, interest rate in cell B1, loan term in cell C1, and payment frequency in cell D1.

Step 4: Calculate monthly payments

Calculate the monthly payments by using the formula ‘=PMT(B1/C1,D1*A1,-A1)’. This formula calculates the monthly payment by taking into account the interest rate, loan term, loan amount, and payment frequency.

Step 5: Create a table of payments

Create a table of payments by listing each month of the loan term in column A. In column B, enter the monthly payment amount calculated in step 4.

Step 6: Determine the interest rate formula

Determine the interest rate formula for your loan. This may vary based on how often your interest rate changes. For instance, if your interest rate changes every six months, you will need to use a formula that calculates the payment amount twice per year.

Step 7: Calculate interest

Use the interest rate formula determined in step 6 to calculate the interest for each period. Multiply the loan balance by the interest rate for each period, using the appropriate formula for that period.

Step 8: Calculate principal

Calculate the principal for each period by subtracting the interest for that period from the loan payment amount.

Step 9: Calculate remaining balance

Calculate the remaining balance for each period by subtracting the total amount paid up to that point from the original loan amount.

Step 10: Create a chart

Create a chart by selecting the payment information you have entered and clicking on the ‘Insert’ tab at the top of the Excel window. From there, select the desired chart type and format it to your liking.

By following these steps, you can create a variable-rate amortization table in Microsoft Excel that will help you gain a better understanding of your loan repayment. This powerful Excel tool can help you make informed decisions about your finances and help you plan for the future.