CREATE AN EVOLUTIVE AMORTIZATION SHEET

jeanV6213813021964 

The basic Excel function that allows you to generate data into multiple rows (or multiple columns) is the SEQUENCE function.  See the attached workbook, including notes on the _Info worksheet.

jeanV6213813021964 

To create an amortization schedule in Excel that dynamically adjusts based on the depreciation period, you can use formulas and tables. Here's a step-by-step guide on how to do it:

1. Set Up Your Data:

• Create a table with headers such as "Period," "Starting Balance," "Payment," "Interest," "Principal," and "Ending Balance."
• Fill in the initial data such as the loan amount, interest rate, and depreciation period.

2. Calculate Monthly Payment:

• Use the PMT function to calculate the monthly payment.

For example:

=PMT(interest rate/12, depreciation period*12, loan amount)

3. Fill in Period Numbers:

• Enter period numbers in the "Period" column, starting from 1 to the total number of periods (depreciation period).

4. Calculate Remaining Columns:

• Starting Balance: This will be the loan amount for the first period and the ending balance from the previous period for subsequent periods.
• Interest: Calculate the interest portion of the payment using the interest rate and the starting balance.
• Principal: Calculate the principal portion of the payment by subtracting the interest from the total payment.
• Ending Balance: Calculate the ending balance by subtracting the principal payment from the starting balance.

• Use formulas to calculate the other columns:

5. Auto-Expand the Table:

• Use Excel's table feature to auto-expand the table as you add more periods.
• Select your data table and go to the "Insert" tab > "Table."

6. Adjusting the Depreciation Period:

• If the depreciation period changes, update the formula in the "Period" column to reflect the new total number of periods.
• Excel will automatically adjust the table size based on the updated formula.

7. Visualize the Data (Optional):

• Create charts or graphs to visualize the amortization schedule for better understanding.

By following these steps, you can create an evolutive amortization sheet in Excel that adjusts dynamically based on the depreciation period. The table will expand or contract automatically based on the number of periods specified.

Additional Link: amortization schedule free Templates from Microsoft

The text was created with the help of AI.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and like it!

This will help all forum participants.