Forum Discussion

jeanV6213813021964's avatar
jeanV6213813021964
Copper Contributor
Mar 19, 2024

CREATE AN EVOLUTIVE AMORTIZATION SHEET

I'M LOOKING FOR THE BEST WAY TO CREATE A SHEET WHOSE THE NUMBER OF LINES CHANGE ACCORDING TO A VARIABLE. FOR EXAMPLE AN AMORTIZATION SHEET WITH A DEPRECIATION PERIOD WHICH CAN CHANGE? AND THE NUMBER ...
Formulas and Functions
NikolinoDE's avatar
NikolinoDE
Gold Contributor
Mar 19, 2024

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:

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.

Resources