Results 1 to 8 of 8

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

  1. #1

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



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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
    Last edited by Marilynnanne; 2011-09-02 at 02:45 AM. Reason: explaining the problem better.

  2. #2
    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.

  3. #3
    Administrator Simon Lloyd's Avatar
    Join Date
    Apr 2011
    Location
    Manchester, England
    Posts
    323
    Articles
    0
    We would need to see the original workbook to fix this for you
    Kind regards,
    Simon Lloyd
    Microsoft Office Discussion

  4. #4
    Here is my original document.
    Attached Files Attached Files

  5. #5
    Administrator Simon Lloyd's Avatar
    Join Date
    Apr 2011
    Location
    Manchester, England
    Posts
    323
    Articles
    0
    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
    Kind regards,
    Simon Lloyd
    Microsoft Office Discussion

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

  7. #7
    Administrator Simon Lloyd's Avatar
    Join Date
    Apr 2011
    Location
    Manchester, England
    Posts
    323
    Articles
    0
    We all have to start somewhere, glad we could be of service!
    Kind regards,
    Simon Lloyd
    Microsoft Office Discussion

  8. #8
    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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •