Pay me now or pay me later

solarMy nephew is looking to lease a solar system for his house. He’s narrowed it down to two options that offer equivalent systems. The question is the pricing structure. One has a low starting monthly payment, which increases each year, and the other has a fixed monthly payment. Which to choose?

Here’s a spreadsheet that compares the annual cost of each, year-by-year: pay me now. Option 1, with the lower initial cost, is less expensive for the first 7 years, but after that Option 2 costs less. Clearly, Option 1 is the choice if he stays in the house less than seven years.

pay me now annual

But he plans to live there more than seven years. Will he save enough on Option 2 in the later years to make it worthwhile to give up the early year savings offered by Option 1?

The simplest way to look at this is to calculate the cumulative costs for each year. Option 1 is less up through 2027, but if he stays in the house beyond that, Option 2 costs less. A more realistic way to look at this is called present value, and it’s based on the fact that money now is worth more than money later. For example, with inflation at 3%, a dollar next year is worth only about 97 cents this year (calculated as 1/(1+.03)). We figure the present value of a dollar each year in the future and see that by 2028 a dollar will buy only two-thirds of what it does now, assuming the 3% inflation rate continues. We use those numbers to get the inflation-discounted cost each year, add it all up, and get the cumulative present cost of each option.

If he plans to keep the house and the solar system at least through 2028, he’s better off with Option 2.

pay me now pv

The choice of the interest rate matters. Businesses will generally use larger rates (called discount rates) to calculate present value to account for risk. If you use a 10% rate, it will be a few more years until Option 2 becomes the less expensive choice.

This isn’t the whole story, of course. Any analysis of a lease needs to include what happens when the house is sold. Is the solar system lease transferable to the new owner? If not, what are the costs to pay off the lease? Since these costs may be far in the future, it makes sense to use present value to consider them.

By the way, if you’re in Southern California on July 10, please join me in Torrance for a free workshop, Instant Insights with Microsoft Excel. http://www.eventbrite.com/e/instant-insights-with-microsoft-excel-tickets-11966908345?aff=es2&rank=0

What’s with the dollar signs in Excel formulas?

dollar signEver noticed cell identifiers with dollar signs in them? Things like $A$1. Sometimes they’re in formulas. Sometimes you select a cell for a dialog box and it shows you something with dollar signs. What’s that all about?

You might try the formulas =A1, =$A1, =A$1, and =$A$1 and find they all give you the same answer, the contents of cell A1. Where you see the difference is when you copy or AutoFill the formula. Excel is smart enough to adjust your formula to its new location, a very convenient feature. But sometimes you don’t want it to do that. That’s what the $ does. It tells Excel “Don’t change the following letter or number when you copy this formula.” So, for example, $A1 will always keep the A, but the 1 may change when copied somewhere else.

Let’s take an example. Here’s a spreadsheet, dollar signs, that calculates total cost of some items, including sales tax.

dollar signs spreadsheet

Everything in columns C through F starting in row 4 is calculated using a formula. In fact, formulas were entered only in row 4, and the rest were copied down the columns using AutoFill. The formula in D4 is =B4+C4. Add the price and the tax to get the total. No dollar signs, so when it gets copied to the other rows it will change accordingly. In D5, for example, the formula is =B5+C5, which is just what you want. This is what Excel does normally, and usually it gives you exactly what you want and saves you a ton of work.

Now look at C4. The formula is =B4*B1, multiply the price by the tax rate. That works here, but it we try to copy it down the column, we’ll get garbage (try it), since it will try to use B2, B3, and so on, instead of B1. We want to make sure we always use the tax rate that is in B1, so we write it as $B$1, and the formula that works for copying is =B4*$B$1.

Check out the formula in E4, that multiplies the total cost of one item by the quantity in E3. It’s =E$3*$D4. See if you can figure out why this works not only when you copy it down the column, but also when you copy it across to column F.

By the way, the references that change (without the $) are called relative references and the ones that don’t change (with the $) are called absolute references. Knowing when to use them will really help you create some big, useful spreadsheets from just a few formulas.

Standard IRA or Roth IRA?

Businessman giving moneyYou’ve just been given a $1000 bonus and you want to save it for retirement. What’s the best place to put it? The difference is in the tax treatment. First, let’s take a look at what happens if you put it in a standard investment. Say your current tax bracket is 25%, but you figure it will be only 15% in retirement. Suppose you expect to get 8% on your investment.

 

 

 

 

Here’s what happens.

non IRAFirst you need to pay taxes on the bonus, which leaves you with only $750 to invest. Each year the investment pays dividends, which are then taxable. When you finally retire in 20 years your $1000 bonus has grown to $2424, even after paying all the taxes. Not bad.

Let’s see if you can do better using a Roth IRA.

 

 

 

 

 

Roth IRA

 

With a Roth you still need to pay taxes on the income before you can invest it, but then it grows tax-free and there are no taxes due when you withdraw it.

You end up with more than $1000 extra due to the tax savings.

 

 

 

 

 

 

 

std IRANow what about a standard IRA or a 401(k)? The tax treatment is the same for either one: You don’t need to pay taxes on the bonus if you put it directly into the account, but you pay taxes on everything you draw out in retirement. Here’s how that turns out.

Better still.

 

 

 

 

 

 

Here’s the Roth or std IRA spreadsheet used for this comparison. Try some different numbers and see what happens. You can change any of the numbers in blue.

If you play around with it a while, you’ll probably notice that

  • The standard IRA is better if you expect to be in a lower tax bracket in retirement. People generally expect to downsize in retirement, so this may be a reasonable assumption. This is also the case if you have Roths or other funds that may be tapped without taxes.
  • The Roth is better if you expect to be earning more in retirement. This may be true if you are early in your career, expect to be earning more in later years, and are saving well for retirement.
  • If you expect to maintain the same standard of living in retirement, then it makes no difference whether you choose the standard or the Roth IRA. You may want to have a mix, to give you more flexibility in tax planning during retirement.
  • Standard investments are never as good as tax-advantaged plans. The exception, by the way, is if you have a standard investment whose growth comes in the form of capital gains, but that’s a subject for another analysis.