Clippy the Paper Clip is letting me down

September 26, 2005 2:29 PM

I need help setting up EXCEL formulas so I can track finances.

I am not a programmer, nor someone experienced with financial software. All I know about Excel is what I’ve been able to teach myself through brute force. I’m sure that Clippy, the Cheerful Paperclip could tell me the answer if I could only figure out a way to ask it in a language it understands.

I’m preparing a spreadsheet to keep track of my finances. I’m trying to pay off some bills and want to know when everything will be paid off based on certain conditions.

I thought Excel would have a template for this kind of thing, but I’m not finding it.

Here’s what I am trying to do. I am trying to pay off the visa and the car loan as well as build up my savings account. I have direct deposit into my savings account, and the car payment is debited directly. In addition, I have three streams of income (in addition to the car direct payment) that I am devoting to paying these debts off. I’ll call these Funds A, B, and C. Ideally, my savings will grow while I’ll pay off these debts.

Here’s the plan. Funds A, B, and C will be applied to the Visa payment until it’s paid off. (It has the highest interest rate and the lowest balance.) When the Visa balance is zero, the money in these funds will be applied to the car loan until it’s paid off. (These funds are currently not attached to anything, so I can use them if an emergency occurs.)

I have made three spreadsheets tracking the payment progress. No problem. It’s set up so that I can enter the funds and track the actual repayment. But what I would like to do is link these spreadsheets so that they are connected to each other. That is, when the Visa bill equals zero, I would like the program to automatically add the Visa payment to the car payment. And this is what I can’t figure out to do, because the date (cell) that the Visa is zero is not fixed. As it is, I will have to manually change the amount in the car payment column when the Visa balance actually is zero.

How do I link the figure in the car payment column to the amount in the Visa balance column? Or maybe give me some tips on how to ask Clippy what I want to do?

posted by luneray to Work & Money (9 answers total)

If your "three spreadsheets" are actually three sheets in a single spreadsheet (and if they aren't, copy and paste can make them so), then you can refer to a different sheet in your formulas:

sheet1!B9 + sheet2!B9 = (the sum of the values in cell B9 in sheet1 and sheet2)

So your formulas could say, for example, "if sheetVisa cell D23 is 0, add the value in sheetVisa cell D22 to sheetCar cell G45".

I also recommend Gnucash, a proper personal finance program that is completely free.
posted by jellicle at 3:10 PM on September 26, 2005

Response by poster: Sorry, some clarification...they are all on the same worksheet within one workbook. That is, car payment takes up columns A to E and Visa takes columns K to S.

Yes, right now I am trying to figure out how to write the car payment formula to say If Visa total=0, then add visa payment to car payment. This become a rather complicated formula, though because the car payment is already a conditional statement, i.e. If car balance>0, then car payment, otherwise zero.
posted by luneray at 3:29 PM on September 26, 2005

I'd be tempted to leave the VISA payment and car payments separate, and have two columns for potential car payment with a third for the total car payment: one could be "if owed on car>0 then base payment"; the second could be "if owed on VISA>0 then 0; else VISA payment"; the third could always equal the sum of the two. So you might have a sheet that shows a column of figures going down to a certain month for the VISA payment, then shifting over several columns to the car payment, once VISA = 0.
posted by Tuwa at 4:25 PM on September 26, 2005

It's perfectly fine in Excel to have nested IF statements.

For example:

=IF(A=1,"A is 1", IF(B=2,"B=2","A is not 1, B is not 2"))

Restated:

=IF(Test1,True,IF(Test2,True,False))

The second "IF" will execute only if Test1 is false.

(And you can always reverse an "equals" test by using
<>
to make it into a "not equals" test, which can be useful because it's much easier to read embedded IFs when the trigger occurs on a false outcome.)
posted by WestCoaster at 4:27 PM on September 26, 2005

<tangent>

Gnucash looks good, but if you're one were simply looking for Excel-based finance-tracking software, you one could check out pearbudget.com.1

It won't do what you were looking for, luneray, but it might be useful for tracking your other expenses.

If you e-mail me a copy of your spreadsheet, I'll see if I can help. Although perhaps the other advice here has set you straight.

——————————
1. That's a self-link, which I've posted before. I'm not just trying to shlep that on you. I do think it'll be useful. And it's free.

</tangent>
posted by Alt F4 at 5:58 PM on September 26, 2005

Alrighty. That "1" was supposed to be a superscripty footnote. Oh well.
posted by Alt F4 at 5:59 PM on September 26, 2005

I misread your question initially and thought you were looking for a way to calculate a formula, if the date was beyond a certain period. I see that others have answered your actual question. However, FWIW, if you do ever need to calculate a formula based on a date, Excel lets you do that, too.
posted by acoutu at 6:42 PM on September 26, 2005

Jellicle - is gnucash only for linux? I can't find a normal windows download anywhere...
posted by kdern at 8:00 PM on September 26, 2005

Isn't there anything in the user submitted templates for Excel on the Office website? It sounds like a pretty common thing that people might want to make spreadsheets for.
posted by easternblot at 9:10 PM on September 26, 2005