Manual Loan Amortization Schedule Without PMT Function in MS Excel

Creating a loan amortization schedule in Excel without the PMT function can seem daunting, but using mathematical formulas and basic Excel functions, you can generate a detailed and accurate amortization schedule. Follow this comprehensive guide to learn how to set up such a schedule step by step.

Step 1: Gather Your Loan Information

To begin, collect the necessary details of your loan:

Loan Amount Principal: The total amount you borrowed. Annual Interest Rate: The interest rate applicable for the entire year. Loan Term: The duration of the loan in years. Number of Payments per Year: Typically 12 for monthly payments.

Step 2: Calculate the Monthly Payment

The formula for calculating the monthly payment is based on the annuity payment formula. Use the equation:

M P times frac{r times (1 r)^n}{(1 r)^n - 1}

Where:

P Loan Amount Principal r Monthly interest rate Annual Interest Rate / 12 n Total number of payments Loan Term in years times 12

(1) Convert the annual interest rate to a monthly interest rate:

r frac{text{Annual Interest Rate}}{100 times text{Payments per Year}}

(2) Calculate the total number of payments:

n text{Loan Term} times text{Payments per Year}

Step 3: Set Up Your Amortization Schedule

Create the following columns in Excel to organize the schedule:

Payment Number: Start with 1 and increase sequentially. Payment Amount: This will be calculated based on the loan details. Interest Payment: Interest paid each month. Principal Payment: Principal amount paid each month. Remaining Balance: Remaining loan balance after each payment.

(1) Enter the initial values: Payment Number: Start with 1 and proceed to the total number of payments (n). Remaining Balance: Start with the initial loan amount.

Step 4: Fill in the Schedule

(1) For the first payment: Interest Payment: Multiply the remaining balance by the monthly interest rate. Principal Payment: Subtract the interest payment from the total payment amount. Remaining Balance: Subtract the principal payment from the previous remaining balance.

(2) For subsequent payments, copy the formulas down the column until you reach the total number of payments.

Example:

Let's assume the following loan details: Loan Amount: $10,000 Annual Interest Rate: 5% Loan Term: 3 years

Calculate the monthly interest rate and total number of payments:

Monthly Interest Rate (r) frac{5}{100 times 12} 0.004167

Total Payments (n) 3 times 12 36

Calculate the monthly payment:

M 10000 times frac{0.004167 times (1 0.004167)^{36}}{(1 0.004167)^{36} - 1} approx 299.71

Set up the Excel sheet with the following structure: Payment NumberPayment AmountInterest PaymentPrincipal PaymentRemaining Balance 1299.7141.67258.049,741.96

Note: Use cell formulas to calculate the interests and principal payments. Drag the formulas down to fill the entire schedule.

Final Notes

Ensure that the cells are formatted appropriately for currency numbers. This manual approach offers a comprehensive loan amortization schedule without using the PMT function in Excel.