CREATE AN EVOLUTIVE AMORTIZATION SHEET

Copper Contributor

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 OF LINES (PERIOD) IS CREATED;

Thanks

2 Replies

Re: CREATE AN EVOLUTIVE AMORTIZATION SHEET

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:

• 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."

• 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.

The text was created with the help of AI.

My answers are voluntary and without guarantee!