Understanding Amortization with Linear Interpolation in Excel

The amortization linear interpolation is a method of estimating the value of a debt or an asset at any given time, based on the known values at two different times. It assumes that the change in value is linear, meaning that it follows a straight line. To use this method, we need to know the value of the debt or asset at the beginning and the end of a period, and the time at which we want to find the value. Then, we can calculate the value by finding the ratio of the time difference to the total period, and multiplying it by the difference in value. This gives us the amount of change in value, which we can add or subtract from the initial value to get the estimated value. This method is useful for finding the interest and principal payments of a loan, or the depreciation of an asset, over time.

Basic Theory

Amortization is often applied to loans, where payments are made at regular intervals. The amortization schedule breaks down each payment into its principal and interest components. Linear interpolation comes into play when we need to estimate values between two known data points.

Procedures

Step 1: Set Up Your Excel Spreadsheet

Create a table with the following columns: Payment Number, Payment Date, Beginning Balance, Payment, Interest Payment, Principal Payment, and Ending Balance.

Step 2: Input Key Data

Enter the loan amount, interest rate, and loan term. Calculate the monthly interest rate and the number of payments.

Step 3: Calculate Monthly Payments

Use the PMT function in Excel to calculate the monthly payment. The formula is =PMT(rate, nper, pv) where rate is the monthly interest rate, nper is the total number of payments, and pv is the present value (loan amount).

Step 4: Fill in the Payment Schedule

Create a series of rows to represent each payment. Fill in the payment date, beginning balance, and payment columns. The beginning balance for each month is the ending balance from the previous month.

Step 5: Calculate Interest and Principal Payments

Use the formulas for interest and principal payments. The interest payment is calculated as =beginning_balance * monthly_interest_rate and the principal payment as =payment - interest_payment.

Step 6: Apply Linear Interpolation

If you want to calculate values between known points (e.g., between payments 5 and 6), use linear interpolation. The formula is =LINFIT(known_y's, known_x's, x) where known_y’s and known_x’s are the values and corresponding positions of the known points, and x is the position for which you want to interpolate a value.

Explanation

Let’s consider a scenario:

• Loan amount: $50,000
• Annual interest rate: 6%
• Loan term: 5 years

Scenario Calculation

1. Set Up the Spreadsheet: Create a table with the required columns and input the relevant data.
2. Calculate Monthly Payments: Use the PMT function: =PMT(6%/12, 5*12, -50000) to find the monthly payment.
3. Fill in the Payment Schedule: Create rows for each payment, calculating the beginning and ending balances.
4. Calculate Interest and Principal Payments: Use the interest and principal payment formulas for each month.
5. Apply Linear Interpolation: Suppose you want to estimate values between payments 5 and 6. Use the LINFIT function.

Excel Table (Partial)

Linear Interpolation Example

Suppose you want to estimate the ending balance for payment 5.5 (between payments 5 and 6).

1. Known Points:
   • Ending Balance at Payment 5: $48,563.81 (Cell G3)
   • Ending Balance at Payment 6: $47,841.89 (Cell G4)
2. Linear Interpolation: Use the LINFIT function: =LINFIT(G3:G4, A3:A4, 5.5)
3. The estimated ending balance for payment 5.5 is $48,202.35.

Other Approaches

While linear interpolation is commonly used, more advanced interpolation methods like cubic spline interpolation can provide smoother estimates. However, these methods may require more complex Excel formulas or additional software tools.