Marilynnanne

2011-09-02, 03:44 AM

I have a simple Loan Amortization Schedule, which was obtained from the Microsoft website.

The first column simply counts the payments: =IF(Values_Entered,A40+1,"")

The next column increments the months: =IF(Pay_Num<>"",DATE(YEAR(Loan_Start),

The next column (beginning balance) captures the balance from the previous line: =IF(Pay_Num<>"",I30,"")

The next column enters the scheduled monthly payment (found in the loan summary): =IF(Pay_Num<>"",Scheduled_Monthly_Payment,

The next column is the "extra payments" (found in the values table): =IF(AND(Pay_Num<>"",Sched_Pay+

The next column is the total payment, which has no formula

The next column is the total payment: =IF(Pay_Num<>"",Total_Pay-Int,"")

The next column is the interest (via the interest rate in the value table): =IF(Pay_Num<>"",Beg_Bal*Interest_Rate/

The next column is the ending balance: =IF(AND(Pay_Num<>"",Sched_Pay+Extra_Pay<

The last column is the cumulative interest, which I understand: =SUM($H$18:$H34)

I have keyed in a 4 year loan period with 12 payments of $260 scheduled payments per year and a start date of 7/23/2009

I can attach it if needed, but am not sure that it is necessary for anyone who really understands the formulas.

I just can't get beyond the "last row" error beyond the 24th payment.

Marilynn

The first column simply counts the payments: =IF(Values_Entered,A40+1,"")

The next column increments the months: =IF(Pay_Num<>"",DATE(YEAR(Loan_Start),

The next column (beginning balance) captures the balance from the previous line: =IF(Pay_Num<>"",I30,"")

The next column enters the scheduled monthly payment (found in the loan summary): =IF(Pay_Num<>"",Scheduled_Monthly_Payment,

The next column is the "extra payments" (found in the values table): =IF(AND(Pay_Num<>"",Sched_Pay+

The next column is the total payment, which has no formula

The next column is the total payment: =IF(Pay_Num<>"",Total_Pay-Int,"")

The next column is the interest (via the interest rate in the value table): =IF(Pay_Num<>"",Beg_Bal*Interest_Rate/

The next column is the ending balance: =IF(AND(Pay_Num<>"",Sched_Pay+Extra_Pay<

The last column is the cumulative interest, which I understand: =SUM($H$18:$H34)

I have keyed in a 4 year loan period with 12 payments of $260 scheduled payments per year and a start date of 7/23/2009

I can attach it if needed, but am not sure that it is necessary for anyone who really understands the formulas.

I just can't get beyond the "last row" error beyond the 24th payment.

Marilynn