Marilynnanne
New member
- Joined
- Sep 2, 2011
- Messages
- 5
- Reaction score
- 0
- Points
- 0
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
Last edited: