+ Reply to Thread
Results 1 to 3 of 3

How to get the right formula

  1. #1
    Registered User
    Join Date
    09-13-2018
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    1

    How to get the right formula

    Hello IAM new to this forum !!

    My question is how do I get the formula to automatically let me know how much utilization of my credit debt do I need to pay down to get a low utilization.

    To be more clearer.....I made a spread sheet of list of all my credit cards with there balances and available credit to determine the credit ratio. What IAM looking for is how to make a column on my spreadsheet that will calculate and let me know how much to pay down to reach my goal. Am I making any sense to anyone...lol plz help

  2. #2
    Forum Expert
    Join Date
    06-26-2010
    Location
    Austin, TX
    MS-Off Ver
    Excel 2010
    Posts
    1,609

    Re: How to get the right formula

    Okay, I'll bite.

    This does not make much sense and I think you are mixing terms. I assume utilization of a credit card is [balance on card]/[card limit]. I am not sure how that is tied to a credit ratio, which is technically defined as your debt divided by your income (which you don't mention above).

    I THINK you are considering 'utilization' and 'credit ratio' to be synonymous. What is also throwing me off is that you have a column for 'available credit' which I define as [card limit]-[balance on card]. I'm not sure what [balance on card]/[available credit] would show (which your text above implies you are calculating).

    I THINK you want to calculate your [balance on card]/[card limit] for each card (let's call this utilization). Then you want to perform some analysis to see what you need to do to achieve a specific utilization across all of your cards. So, if you want to reach 5% utilization, what cards do you need to pay off to reach that goal?

    The problem is that this has many solutions. Here are two simple examples:
    1) Calculate the goal for each card you have (i.e. 5%) and just have each card's balance match that goal. The problem here is if some cards are already below your goal of 5%, you'd have to carry that 'credit' on to other cards.
    2) Probably the simplest is to keep zeroing out your remaining balance until you can reach 5% and then just subtract whatever you need to do. You'd want to pay off the cards with the highest APR/penalties first.

    Example for the second solution - Note your data must start on row 2 since the equation needs an empty 'top' cell for column C:
    Column A has your balance for each card
    Column B has your card limit for each card
    Column C will have this equation:
    =MEDIAN(SUM($A$2:$A$5)-SUM($C$1:C1),0,MIN(A2,SUM($A$2:$A$5)-SUM($C$1:C1)-rate*SUM($B$2:$B$5)))

    rate is the percentage you want to get to, e.g. 5%.
    Pauley
    --------
    If I helped with your issue, I'd appreciate a rep bump (hit the '*' icon to the bottom left of this post).

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    47,812

    Re: How to get the right formula

    Hi, welcome to the forum

    Please take a moment to read the forum rules and then amend your thread title to something descriptive of your problem - not what you think the answer might be. (think google search terms?). Once you have done this please send me a PM and I will remove this request. (Also, include a link to your thread - copy from the address bar)

    Many members search our previous posts, and thread titles play a big part of the search. I doubt anybody would do a search based on your title?

    To change a Title on your post, click EDIT POST then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

    (note: this change is not optional )
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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