+ Reply to Thread
Results 1 to 3 of 3

Loan Calculator Framework - Extra Repayments, Multiple Loans

  1. #1
    Registered User
    Join Date
    09-07-2009
    Location
    Sydney
    MS-Off Ver
    Excel 2007
    Posts
    26

    Loan Calculator Framework - Extra Repayments, Multiple Loans

    Hi All

    Got the basic loan amortization with extra repayment functionality sorted out, now trying to add some functionality. Want people to give me some ideas on how they would build this.

    The driving question is
    In what order do I pay extra on the loans out into order to pay all loans out earlier.


    SO..
    I have a spreadsheet with 3 loans on 3 different sheets.
    I have some spare cash and want to make extra repayments.
    I apply it to a loan. If a loan gets payed out, I then 'snowball' the payments into the remaining loans.

    How to do this???

    I was thinking a messy IF statement in the extra repayment column of each loan that
    checks if the principle remaining is above zero (ie not payed out), and if so, then checks to see if its next in order to be payed out.
    If it is then it applies the extra repayment amount.
    If not then just use 0 as the extra payment amount.

    Excel goes off and makes the extra repayments. I record the results, then repeat for a different order (3,2,1).

    Still a bit laborious - something that goes through the different orders, and returns the best one would be awesome.

    Any other ideas on how to provide this functionality. Scenarios might be an option but I have not used this tool before.

    A nagging thought is that in the real world some loans start earlier than others, so cant just put all loans side by side.
    A way around this would be to find a common date in all 3 loans then manually move rows down until the same date is on the same row for all loans.

    Cheers

    Sam
    PS
    Not trying to consolidate the loans. Plenty of those calculators out there.
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    06-18-2009
    Location
    US
    MS-Off Ver
    Office 2003
    Posts
    23

    Re: Loan Calculator Framework - Extra Repayments, Multiple Loans

    Hi samtoucan,

    I took a look at your spreadsheet sample, and I think you can download a readymade spreadsheet template with all you need on, and begin to use it for your requirements. This is a loan calculator providing 3 scenarios: monthly payment amount, the interest rate and the number of payments. Please follow the link below to check it out.

    http://www.spreadsheetzone.com/templateview.aspx?i=21

  3. #3
    Registered User
    Join Date
    09-07-2009
    Location
    Sydney
    MS-Off Ver
    Excel 2007
    Posts
    26

    Re: Loan Calculator Framework - Extra Repayments, Multiple Loans

    Hi Suzzy

    thank you for the response but although my original spreadsheet is flawed, the link is only for a single loan amortization table.
    Unfortunately it does not answer the million dollar question.
    In what order do I pay extra on the loans out into order to pay all loans out earlier.

    Note I am not actually looking for that answer (although if someone posted the formula I would be darn grateful). Instead I am actually looking for ideas on how to solve that problem.

    Since there are 3 loans there are 6 possible scenarios
    123
    132
    213
    231
    312
    321

    Am I going to need a messy IF statement, OR say 6 sets of 3 loan sheets, and say an overview sheet that gives the final date each set of loans is paid out.

    Ideas - thats what I am looking for.
    Last edited by shg; 10-02-2009 at 12:22 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1