Daily Dose of Excel

I’m a big fan of the Toyota Production System. I was thinking that I should find a mutual fund that invests in lean companies, but I couldn’t find one. I searched for “best lean companies” to see if I could get a list, and found Who Are the Best Lean Companies at leanblog.org, a blog I read every day.

I took all of the companies in the top 10 for whom I could find historical data. I compared buying one share of each of those companies to buying one “share” of the S&P500 over the same period. The lean companies outperformed the index.

D9: =RATE(C1-B1,0,-B9,C9)*365
D11: =RATE(C1-B1,0,-B11,C11)*365

Are those formulas right? I’m trying to get an annual return.

I couldn’t stock-pick my way out of a paper bag (I’m looking at you Buckle; I’m looking at you Inacom), so don’t take investing advice from me.

The other day J-Walk posted How to Retire at Age 40:

If you were to take 20% of your annual income starting at age 20 and put it in a fund following the S&P 500 Index ($INX), that fund continued to grow at the long-term historical rate (12%) and you received a 4% raise each year, you could walk away from your job and live off the interest at age 41 matching your current salary — or quit at 43 and be able to give yourself a 4% “raise” each year from the interest, which is probably the better plan because it combats inflation. Original Source

Then today, he posted Retirement Revisited which proves out the math.

I’ve been working on this also and wanted to post my results too. Instead of assuming that the S&P 500 returns 12%, I used actual returns and based it on my actual life. Sort of.

I started at Standard & Poor’s - Indices S&P 500 and clicked on the “S&P 500 historical returns” link to download the Excel (xls) file. I don’t remember how much I made when I was 20, but it wasn’t much because I was still in college. In fact, I think I was a third-year freshman. I turned 20 in June of 1989 and let’s assume I made $15,000 per year. I’ve hid some columns and rows, but here’s the gist of it:

Column D is the monthly return for the S&P 500 for all 240 months between my 20th and 40th birthdays. Since I’m not 40 yet, the last 19 months are the average annual return of the index from June ‘89 to October ‘07 (7.98638%). That was computed thusly:

=RATE(221,250,0,-125150.57,,0.1)*12

The interest rate for 221 months when I start at zero, put $250 per month in, and end at $125,150.57. The $125k is what I get from this worksheet when I don’t increase the monthly pay by 4% and is not shown in the screen shot.

Column N is just a counter showing the month. Column O is the monthly amount saved. The formula in O9 is

=$O$1/12*0.2*(1.04^(INT(N9/12)))

$O$1 is the annual salary at age 20. Divided by 12 months. Times 20% savings rate. Increased 4% every 12 months.

Column P shows the interest I earned for the period and column Q is the balance in my investment account.

P: =Q10*D9
Q: =Q10+O9+P9

I’ll have $196k to live on. Let see if it will last.

Nope. If I live until 75, I’ll be $3 million in debt. At age 40, I’m making about $33,000 per year and that’s the standard I need to maintain. If I pull out 1/12 of that every month, I’ll be broke sometime in September of 2017. Damn, the Huskers might be good by then and I won’t have any money to go to the games.

Column B: =MONTHLYRETURNS!$O$1/12*(1.04^20)
Column C: =D3*MONTHLYRETURNS!$D$9 $D$9 is the average return of ~8% per year from the other sheet.
Column D: =D3-B4+C4

It’s not prettied up, by you can download SP500Monthly.zip.

I’ve never bought fireworks in my life. Where I grew up they were illegal (although that seemed to deter few people) and my dad would never allow it. Yesterday, my son bought fireworks for the first time in his short life. I guess I’m less of a hard ass than my dad. My only stipulation was that he had to spend his own money and that an adult had to be present when he lit them.

I am fundamentally opposed to buying fireworks. I don’t oppose other people buying them, mind you. In fact, I need other people to buy them. I can sit in my driveway and watch thousands of dollars of other people’s money go up in smoke, get just as much entertainment as if I’d bought them, and not spend a penny. Did I mention I’m an accountant?

The boy spent $35.34 on fireworks this year. Had he invested that money in an index fund that returns 10% over time, he would have $8,084.43 when he’s 65.

=FV(0.1,57,0,-35.34)

If he continues to buy fireworks until he retires, and the cost or quantity of fireworks he buys is 5% more than the previous year, he will have spent $11,271.88. If he invested that money instead, he would have $165,930.57.

A6: 8
A7: =A6+1
B6: 35.34
B7: =ROUND(B6*1.05,2)
C6: =FV(0.1,65-A6,0,-B6)

He thoroughly enjoyed lighting his fireworks, but I don’t know if was $166,000 worth of fun.

You may have received an invoice from a vendor that had payment terms that offered you a choice. An example of such a payment term is 2/10 Net 30. This means that if you pay the invoice within 10 days, you can take 2% off of the invoice amount. Otherwise the entire invoice amount is due in 30 days.

Whether taking this discount and paying early is a good idea depends on your marginal borrowing rate (the interest rate you have to pay on the next dollar that you borrow). If after converting the discount into an annual percentage rate, that rate is greater than your marginal borrowing rate, you should take the discount even if you have to borrow the money to do so. It’s almost always advantageous to pay early and take the discount.

As an example, assume you’ve received an invoice for $10,000 with terms of 1/10 Net 30, or a 1% discount if you pay in 10 days.

Ten thousand is the gross invoice and the discounted amount is $9,900, or a 1% discount. There are three time periods that you need to consider. Most calculations will only use two, but don’t you fall into that trap. You need to know the number of days to pay to get the discount (10), the number of days to pay according to the terms if you don’t take the discount (30), and the number of days in which you will actually pay if you don’t take the discount (43 in this example). That last number would likely take into account your history with this vendor. If you have no history with this particular vendor, you should consider how timely you normally pay your bills. Some companies pay on time religiously, while others squeeze as much free interest out of their vendors as possible. Whatever your situation is, be sure to use accurate numbers, not idealistic ones. The formulas are:

B10: =360/(B7-B8)*((B3-B4)/B3)
B11: =360/(B6-B8)*((B3-B4)/B3)

According to the terms, the vendor is offering to borrow $10,000 from you for 20 days and pay you $100 interest. That works out to about an 18% interest rate. I think that’s what Citibank used to charge me when I was in college. In our example, though, you know that you have historically paid this vendor in an average of 43 days without incurring any late fees or other kinds of penalties. It’s reasonable to assume that you wouldn’t pay them in 30 days since you never have. You’re really not loaning them the money for 20 days (30 - 10) but 33 days (43 - 10). If you can borrow $9,900 at something less than 10%, you should do it and pay the bill early.

The other side of this coin is the consideration of the vendor, that is, the company trying to collect the bill. Why on Earth would they offer a discount of this magnitude. Payment terms of 2/10 Net 30 is pretty common and is the equivalent of about 36%. If you could make 36% on your money all day long, you could buy and sell Bill Gates and Warren Buffett. There are a couple of reasons companies would offer you this seemingly great deal.

First, they may not be able to borrow money at any interest rate. Their consideration in the above scenario is the same as yours. That is, if they can borrow money at less than 10%, they don’t need your money early and shouldn’t offer the discount at all. If however, they are maxed out and can only borrow from Vinney the loan shark, then your money is cheap (compared to broken knee caps).

Second, there’s a risk factor. The longer a receivable is outstanding, the greater a chance that it will never be collected. There is a cost to that risk, although it’s hard to quantify. “A bird in the hand” is the financial doctrine that applies and that indicates the cost is around 50%.

There’s also a risk that the customer will take the discount and still pay in 45 days. If you’re dealing with very large companies that have a tendency to dictate terms to their smaller suppliers, save yourself the trouble and don’t even offer the discount.

I need some help from the math majors out there. The question of the week a couple of weeks ago concerned making charitable contributions and taking the tax deductions. For those not familiar with the US tax code, money paid to certain charities is deductible for income tax purposes, generally within the year paid.

The situation: I want to make a $1,000 charitable contribution in the first part of 2005 and I decide that I want to do this the last week of 2004. There exists some date in 2005 before which it makes sense for me to make the contribution on December 31, 2004 thereby getting the deduction earlier than if I had made it when planned. Positive and negative cash flows sounds like a problem for IRR (Internal Rate of Return) to me.

Some assumptions: I make 10% on my money and I pay a total tax rate of 33%. Also, I get a refund three months after year end.

I set up the cash flows and calced the IRR but didn’t get the expected results. I wanted to see if I could figure it out manually to help me find my error. Manually, I come up with April 30th, i.e. contributions I plan on making before April 30th should be made before the end of the year, and those after should be made when planned. My question is: Why don’t the IRRs equal when the cash flows from the manual method equal?

Column B - I make the payment on Dec 31st and get the refund on Mar 31st. Thereafter, I earn 10% on my money.

Column D - I earn money on my $1,000 until I pay it out, then get my refund three months after I file my 2005 return.

Columns F and H just show the cash out because IRR is supposed to take the time value of money into consideration.

You can download CharityIRR.zip to see how it works. And if you know where I have erred, please educate me.