+ Reply to Thread
Results 1 to 5 of 5

Calculating a Line of Credit

  1. #1
    Registered User
    Join Date
    08-13-2015
    Location
    Toronto
    MS-Off Ver
    Office 365
    Posts
    14

    Calculating a Line of Credit

    Hi There. I have a cash flow chart (see attached) showing monthly costs over a 12 month period in row 3 and the cumulative costs below that in row 4. I have a line of credit in row 5 that kicks in in month 4 which will cover part of the cost from month 4-12 but not all of it. Iím looking for a formula I can drag from B5-M5 that has the following conditions:

    -Only start using the line of credit in month 4, so B5-D5 would show 0.

    -From month 4 the line of credit should cover all of the monthly cost until the line of credit runs out.

    -In the month that the line of credit runs out, if it can cover part of the cost for the month show the amount it can cover. For example, if I have a $280,000 line of credit and $50,000/month costs, months 4-8 will would show $50,000 being drawn from the line of credit per month but in month 9 the line of credit will only be able to cover $30,000.

    -In the following months after the line of credit is completely depleted, the cells in row 5 (K5-M5) should show 0.


    PS. Row 6 just shows the cash required when the line of credit doesn't cover the cost so cells E6-J6 will change once the formula is input into row 5.
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    42,071

    Re: Calculating a Line of Credit

    See if this works for you:

    Excel 2016 (Windows) 32 bit
    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    L
    M
    1
    Month
    Month
    Month
    Month
    Month
    Month
    Month
    Month
    Month
    Month
    Month
    Month
    2
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    3
    Monthly Cost
    -50,000
    -50,000
    -50,000
    -50,000
    -50,000
    -50,000
    -50,000
    -50,000
    -50,000
    -50,000
    -50,000
    -50,000
    4
    Cumulative Cost
    -50,000
    -100,000
    -150,000
    -200,000
    -250,000
    -300,000
    -350,000
    -400,000
    -450,000
    -500,000
    -550,000
    -600,000
    5
    Monthly Line of Credit
    0
    0
    0
    50000
    50000
    50000
    50000
    50000
    30000
    0
    0
    0
    6
    Cash
    50,000
    100,000
    150,000
    150,000
    200,000
    250,000
    300,000
    350,000
    420,000
    500,000
    550,000
    600,000
    7
    8
    9
    10
    Max Line of Credit
    280000
    11
    Line of Credit starting Month
    4
    Sheet: Sheet1

    Excel 2016 (Windows) 32 bit
    B
    5
    =IF(B2<$B$11,0,IF(SUM($A5:A5)=$B$10,0,IF(($B$10-SUM($A5:A5))<ABS(B3),$B$10-SUM($A5:A5),ABS(B3))))
    Sheet: Sheet1
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Registered User
    Join Date
    08-13-2015
    Location
    Toronto
    MS-Off Ver
    Office 365
    Posts
    14

    Re: Calculating a Line of Credit

    It worked! Thanks a million!!!

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    42,071

    Re: Calculating a Line of Credit

    You're welcome!

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

  5. #5
    Registered User
    Join Date
    08-13-2015
    Location
    Toronto
    MS-Off Ver
    Office 365
    Posts
    14

    Re: Calculating a Line of Credit

    It's done.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. College Credit Template - calculating the # of class type with course credits.
    By excelstone in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-21-2015, 04:00 PM
  2. Line of Credit Schedule
    By pranjal79 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-09-2014, 01:42 PM
  3. Graph month end balance for a credit line
    By ExcelINN in forum Excel General
    Replies: 1
    Last Post: 06-02-2014, 07:40 AM
  4. Create two line journal entry from one line, two different column for debit & credit
    By ditto135 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-03-2011, 03:01 PM
  5. [SOLVED] I need a line of credit amortization schedule
    By Sarah in forum Excel General
    Replies: 0
    Last Post: 06-09-2006, 03:10 PM
  6. Calculating the Remaining Credit after Each Purchase
    By ExcelTip in forum Tips and Tutorials
    Replies: 0
    Last Post: 08-29-2005, 11:03 AM
  7. Calculating credit card debt, interest , and payments
    By deeosu in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-18-2005, 04:06 PM

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