OT Spreadsheet capability?

I'm trying to create an open office spreadsheet to calculate paying off debt asap. I've never hand any kind of classes on spreadsheets, just create what
I need as I go. My debt payoff spreadsheet is beyond what I know how to do. This is a useful tool but also a challenge playing with and learning more about spreadsheets.
Here is how I'm trying to get it to work:
I enter an interest rate of all my debts, a minimum payment and a balance.
I want to have set amount that I will use to pay off the debt.
So, there is a row of balances, the next row is a row of payments
The row of payments pays the minimum balance on each account except the highest interest account gets the extra payment.
If the next scheduled payment is greater than the account balance, it calculates to pay the balance plus interest.
After a balance is paid off, it automatically applies all the extra money to the next highest interest rate account.
Example
House Car Credit 6% 7% 12% 50,000 20,000 5,000 Pay per month $1200 500 400 100 Minimum Payments 500 400 300 Calculate Payment to make 49,900 19,780 4,780 Previous balance + Interest - Payment (not actual calculations here but will be on spreadsheet) 500 400 300 Calculate Payment to make 49,800 19,560 4,560 Previous balance + Interest - Payment (not actual calculations here but will be on spreadsheet)
......
500 400 300 Calculate Payment to make 48,540 16,470 230 Previous balance + Interest - Payment (not actual calculations here but will be on spreadsheet) 500 465 235 Calculate Payment to make It calculated balance plus interest for last payment, put excess on next 48,250 16,230 0.00 Previous balance + Interest - Payment (not actual calculations here but will be on spreadsheet) 500 700 0.00 pays minimum on house and remaining budget on highest interest that carries a balance 48,050 15,710 0.00 Previous balance + Interest - Payment (not actual calculations here but will be on spreadsheet)
I have most of this working except automatically shifting the money over to the next highest interest account, currently I'm making payment changes manually except the payoff. =IF(balance>payment;pay normal; pay bal + interest)
I need to add something like: IF(highest interest rate with >0 balance; pay remainder; pay minimum)
The usefulness of this comes in seeing the effect of making additional purchases versus waiting.
RogerN
Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload
This is my example with what I currently have in my spreadsheet
This is pasted from the spreadsheet so I'm not sure how it will come out.
House Car Credit $500.00 $400.00 $100.00 6.00% 7.00% 12.00% $500.00 $400.00 $300.00 $50,000.00 $20,000.00 $5,000.00 $500.00 $400.00 $300.00 $49,750.00 $19,716.67 $4,750.00 $500.00 $400.00 $300.00 $49,498.75 $19,431.68 $4,497.50 $500.00 $400.00 $300.00 $49,246.24 $19,145.03 $4,242.48 $500.00 $400.00 $300.00
...
$45,575.67 $14,951.86 $392.39 $500.00 $400.00 $300.00 $45,303.55 $14,639.08 $96.31 $500.00 $400.00 $97.28 $45,030.07 $14,324.48 $0.00 $500.00 $400.00 $0.00
It works fine for figuring the final payment (calculated amounts will be changed to fit the actual balance from statements) The main thing it doesn't do is automatically add the money to the car payment after the credit card is paid off. I'm trying to do this without adding another row.
Sorry, not as fun as arguing politics :-)
RogerN
Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload
Roger, I would think that you would be better off using any of available mortgage calculators that calculate accelerated mortgage repayments in a spreadsheet.
That said, openoffice has support for mortgage fnuctions:
http://www.openofficetips.com/blog/archives/2004/10/financial_funct.html
For higher interest loans, paying them off is the greatest and safest investment that you can make.
i
Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload

I can use the spreadsheets built in financial functions, and I do for calculating a mortgage payment but I would still like a way to allocate dollars over minimum to the highest rate loans as they are paid off. Right now when playing "what if" I have to manually shift the $$$ as one account is paid off. This works fine but I would prefer to figure out how to make the program do it automatically. Every time I try something different I have hand enter all the payment shifts from one account to the other.
I need some ultimate nested IF statement I believe:
The rules are
all monthly payments add up to a monthly debt budget amount.
All debts are paid at minimum except if either the balance due is less than minimum or this debt has highest interest rate.
RogerN
Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload
RogerN wrote:

Does Open Office include its own version of VBA (Visual Basid for Applications)? Because what you wrote sounds like something amenable to some kind of procedural language, and M$ supports it (VBA), AFAIK.
Good Luck! Rich
Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload

Sometimes "Select Case" decision trees work better than "If..Then..Elseif".
jsw
Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload

http://wiki.services.openoffice.org/wiki/Documentation/BASIC_Guide
jsw
Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload
On Tue, 7 Jun 2011 21:20:50 -0500, "RogerN"

=============for 429k hits see
http://www.google.com/search?q=open-office+template+download+pay-off+OR+amortization&hl=en&newwindow=1&num 0&lr=&ft=i&cr=&safe=images&tbs
-- Unka George (George McDuffee) .............................. The past is a foreign country; they do things differently there. L. P. Hartley (1895-1972), British author. The Go-Between, Prologue (1953).
Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload
RogerN wrote:

Start from one of the paper spreadsheets you're using now.
A computer can't do anything for you that you can't already do on paper, the computer is just faster.
Using your paper spreadsheet for reference, think of each box on the computer spreadsheet as a replica of the corresponding box on the paper spreadsheet.
In "edit" mode, just type in the equation or formula for what gets done on the paper spreadsheet to derive that value.
Think up a clever name, and save the spreadsheet as a file. :-)
Have Fun! Rich
Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload

Some unsolicited advice if you have multiple debts:
make the minimum payment on all except the one with the lowest payment- throw everything you can at it. When that one is paid off, do the same with the next lowest payment.
Dave
Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload
On Wed, 8 Jun 2011 05:01:32 -0700 (PDT), Dave__67

Good advice, but I'd add that keeping an eye on the account with the highest interest and paying it down more quickly can be the best route. Do the math and the path becomes easier.
The best bet is not to go -into- debt, but when you need to, go short term.
-- Fleas can be taught nearly anything that a Congressman can. -- Mark Twain
Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload

There are online calculators that you can enter your balances, interest rate, and minimum payments into; and they will tell you how much to pay on each account based on whether you have a certain amount of cash available our just want to squeak by paying the minimum amount (useful if you lose your job or something). I use the one from CNN/Money: http://cgi.money.cnn.com/tools/debtplanner/debtplanner.jsp You can put your mortgage payment, car payments, etc all in the calculator as the "Minimum Payments" for those accounts; and the calculator will give you a payment plan to minimize your total interest expense. You don't really need to keep updating it every month if you stick to the payment plan it gives you and don't run up a bunch of additional debt somewhere.
Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload
On Tue, 07 Jun 2011 21:20:50 -0500, RogerN wrote:

...
[snip example, which has column names, interest rates, and minimum payments as in the spreadsheet attached below]

This can be done with min, max, and value-of-comparison operators; eg, if cell B8 is current Car balance, and B9 is going to be the next Car payment, we want to pay the smaller of (usual payment, final payment), where 'usual payment' is either 'minimum payment' or (when C8 is not positive) 'extra money'. The expression max(B$5; (C8<=0)*($A$6-A9)) gives 'usual payment', and the formula =MIN(MAX(B$5;(C8<=0)*($A$6-A9));B8*B$4) gives the desired result. (Cell B4, addressed row-absolutely by B$4, has value ~ 1 + Car-payment-monthly-interest-rate, for final-payment calculation.)

Obviously you should pay off the debts instead of buying more. :)
Anyhow, here's an example of some spreadsheet output, with some notes in [] or footnotes marked like [4] that explain formulas in cells.
House    Car    Credit 6.00    7.00    12.00 [Annual interest rate, %] 0.01    0.01    0.01 [ A2/1200, B2/1200, ...] 1.01    1.01    1.01 [ 1+A3*1.01, ...] [4] 500.00    400.00    100.00 [ Min payments] 1200.00         [ Monthly total]          3500.00    2500.00    750.00 [ Beginning balances] 500.00    400.00    300.00 [ Calculated payments] [9]          3017.50    2114.58    457.50 [ Calculated balances] [11] 500.00    400.00    300.00          2532.59    1726.92    162.08 500.00    400.00    163.71 [ Calc'd payments, one being final]          2045.25    1336.99    0.00 500.00    700.00    0.00          1555.48    644.79    0.00 500.00    648.59    0.00          1063.25    0.00    0.00 1068.62    0.00    0.00          0.00    0.00    0.00 0.00    0.00    0.00
[4] The factor *1.01 overpays last interest by a few cents because otherwise new balance could be a tiny fraction of a cent positive, which would prevent zero-balance detection from working.
[9] Formulas in A9-C9 can be copied and pasted into rows 12, 15, ... A9 is =MIN(MAX(A$5;(B8<=0)*$A$6);A8*A$4), B9 is =MIN(MAX(B$5;(C8<=0)*($A$6-A9));B8*B$4), and C9 is =MIN(MAX(C$5;(D8<=0)*($A$6-A9-B9));C8*C$4)
[11] Formulas in A11-C11 can be copied and pasted into rows 14, 17, ... (ie highlight the numbers of rows 9-11, ctrl-C, move to 12, ctrl-V, move to 15, ctrl-V, etc) A11 is =MAX(A8-A9+A$3*A8;0) B11 is =MAX(B8-B9+B$3*B8;0) C11 is =MAX(C8-C9+C$3*C8;0)
--
jiw

Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload

<snip>
First of all, thanks for the formula, I'll cut and paste them in my spreadsheet to see how they work.
Here's part of my situation. My daughter is going to college this fall and I want to get her a car that is economical, safe, and reliable. I have a 96 Honda Civic I'm getting an estimate to get around $2000 worth of work done to make it safe and run right.
I also would like a better gas mileage car to save me some money. I drive 66 miles per day, 6 days a week most weeks, my work truck gets around 19-20 mpg. I also like having a spare vehicle in case mine or my wife's auto needs serviced. So if I get something that gets ~34+mpg it will save me $150+ per month at current gas prices. A majority of the miles on a more fuel efficient vehicle also makes my truck I drive now (4WD Ford Ranger) last more years. I'm thinking only drive truck on snow days and when needing to haul something. So, I'm doing calculations of new versus used, etc. I drive 17-20K miles per year for work.
It looks like my best financial strategy is to buy a used car that should last at least 2 years, it will save enough gas to almost pay for itself and small monthly payments will allow me to pay off my highest interest debts before the car is worn out. So, I'm trying different car payments versus how long the car should last, etc. Anyway, even if I were to buy a new Honda Civic or Toyota Corolla price range vehicle, I should be able to pay off everything within 5 years, a new car, all creditors, house, everything except new debt.
RogerN
Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload

Polytechforum.com is a website by engineers for engineers. It is not affiliated with any of manufacturers or vendors discussed here. All logos and trade names are the property of their respective owners.