Use Excel to help Visualize your Money

[ascorbic]

One thing that has really helped me save more and pay off debt better is excel. Excel is helping me keep a percise and accurate representation of how much money I owe. From that I force myself to better deal with my debt. I calculate how much money I will have left over after paying my monthly expenses. I also include a "Savings" bill along with the rest of my other bills. By treating savings as a bill and not as savings I am actually saving more because I changed my habits to be an ontime bill payer. This is what I had to do to be better about sabing, as strange as it is, it works for me. You could also have multiple savings bills with different limits for different things like an emergency fund, and other things (in which case higher utilization would be better). It feels good to pay bills and know I am on the track to a healthy financial lifestyle.

Here are the columns I track for my credit cards

Balance - Current Balance, I am updating manually every month
Limit - Credit Limit on the card
Interest Rate - Current Interest Rate
Utilization - Percent of the card being used, higher means bad
Minimum Payment - Minimum I must pay (set by me or them)
Interest Charged - The interest which will be charged on the account on the next statement
Total Number of Payments - Total amount of payments required based on minimum to get the balance to zero
Total Interest - Total interest spent while getting balance to zero, again based on minimum

I sum up the balance of all my bills and do calculates off of that to determine what I will have left. After figuring out how much I have left I take the additional and apply some to the amount I am already saving and some to additional bills.

Here are some formulas and vba which I use (assuming you have the same columns as above).
Utilization=A2/B2*100
Interest Charged=ROUND(A2*(C2/12/100), 2)
Total Number of Payments=CalculateNumberOfPayments(A2, C2, E2)
Total Interest=CalculateTotalInterest(A2,C2,E2)

Function CalculateNumberOfPayments(currentBalance As Long, interestRate As Long, paymentAmount As Long)

    Dim numberOfPayments As Integer
    
    numberOfPayments = 0
    
    While (currentBalance > 0)
        currentBalance = currentBalance + Round(currentBalance * (interestRate / 1200), 2) - paymentAmount
        numberOfPayments = numberOfPayments + 1
    Wend

    CalculateNumberOfPayments = numberOfPayments

End Function

Function CalculateTotalInterest(currentBalance As Long, interestRate As Long, paymentAmount As Long)

    Dim totalInterest As Long
    
    numberOfPayments = 0
    
    While (currentBalance > 0)
        Dim termInterest As Long
        termInterest = Round(currentBalance * (interestRate / 1200), 2)
        currentBalance = currentBalance + termInterest - paymentAmount
        totalInterest = totalInterest + termInterest
    Wend

    CalculateTotalInterest = totalInterest

End Function

So first in my excel spreadsheet I have listed all my credit cards with all of the information from above listed out. I subtotal the balance, limit, utilization, minimum payment, interest, and total interet paid. As you can tell from the columns I am tracking this is the most important debt for me to knock out right now.

Directly below that I fill in my student loans. I haven't been keeping balance up to date because I am focusing on credit card debt, student loans are just a bill right now. I subtotal these loans.

Then next I have utilities and rent which also get subtotaled and then an extras area which also gets subtotaled. All of the subtotals (credit, student loans, utilities and extras) get totalled for my grand total which I need to spend every month.

For visualization purposes all my subtotal lines have a light gray background and the grand total has a darker gray background. Ohh I also highlight the balance of the next credit card I allocate additional money to. I am not snowballing, I am using my own methods mostly based on the next amount of interest being charged. This will save the most money in the long run by systematically allocating funds towards the biggest expense.

[snshijuptr]

I started using Google Docs instead of Excel so I can share the budget with my husband. I have a graph that shows how much was spent in red on top of how much is left in blue. This creates a bar that slowly turns red. I also put above it the percent of our way through our money and the percent of our way through the month.