Siimple, Standard Amortization, but can only go to 24 payments.

[Marilynnanne]

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

[Marilynnanne]

The second column actually is: =IF(Pay_Num<>"",DATE(YEAR(Loan_Start),MONTH(Loan_Start)+(Pay_Num)*12/Num_Pmt_Per_Year,DAY(Loan_Start)),"")
I downloaded another schedule, which uses the same formula, and allows me to go beyond 24 lines, but would still like to see this one work.

[Simon Lloyd]

We would need to see the original workbook to fix this for you

[Marilynnanne]

Here is my original document.

[Simon Lloyd]

Ok, simple enough for you to fix, the worksheet doesn't use dynamic named ranges (you can find out more here http://www.contextures.com/xlnames01.html) it simply has Named ranges, standard named ranges do not contract and expand with data, so the ones that are in are hard coded to row 41. To change these go to INSERT>NAMES>DEFINE in the box that pops up just click a name once, in the bottom textbox you will see the range it refers to, just change the 41 in all of them to another figure that suits you so say 60, and all will be well

[Marilynnanne]

Well, this looks ridiculously easy. Thank you for rescuing me from myself...

[Simon Lloyd]

We all have to start somewhere, glad we could be of service!

[Marilynnanne]

FYI, I actually have Excel 2007, and found it in the Name Manager under the Formula tab. All of the fields were there, and I changed each of the fields. It is working now. Thanks again.