Archives For Financial Math Basics

I received this email last week:

Hi, I was wondering if you could help me out and explain why each month the amount of my car payment applied to interest goes up and down as opposed to gradually going down? Does the time of the month you make your payment affect this? Or something else?

Thanks,

L

After a couple of email exchanges, I got the following information:

Amount financed: $16,788
Interest rate: 12%
Term: 6 years (72 months)

I plugged those into a spreadsheet to find that his monthly payment should be around $328.21. He told me his actual payment is $331.99 per month. Most likely, there’s some program included in his monthly note. He also sent me a record of his payments:

As you can see, his first payment had a ton of interest. That’s most likely due to the fact that they probably put off the first car payment for 45 days (at least that’s what I came up with). My theory is that interest on this loan is charged on a daily basis. In this case, they divided 12% by 365 and then multiply that figure by the outstanding balance and add that amount to the balance. Like this:

Then, on the date the payment is made, the current balance is reduced by the amount of the payment and the interest charge is calculated on that balance. The interest portion of the payment is simply the sum of the daily interest charges since the last payment. So, if he goes longer between payments, there will be more interest for that particular month. I came pretty close to getting the same numbers as this reader sent me.

He then asked me if there was a way to reduce the interest charges by sending in his payments early. That will reduce his interest charges slightly but to REALLY make a difference, he needs to pay more towards his principal each month. That would give him the most benefit. And, since his interest rate is 12%, it would be very wise of him to do so. According to my numbers, he will end up paying over $6,900 in interest on this loan. He only financed $16,788.

The only good thing I can say is at least he bought a Honda. I just hate seeing people with these kinds of loans.

Yesterday I received an email from a guy who needed help with figuring out his personal rate of return using Excel’s XIRR formula. He had just started contributing to his 401(k):

3/26/2010 – $188.50
4/8/2010 – $377.02
4/16/2010 – Balance of $593.54

The XIRR formula requires both negative and positive numbers in order for it to work. So, the best thing to do is to enter contributions as negative numbers since they are “outflows” to you. Like this:

Then, the ending balance would be a positive number. I explained this to the reader and this morning he sent me back an email telling me that he did it but got a return of 314%, which didn’t seem correct because his company was telling him the answer was 8.53%.

Both answers are correct. This is because the XIRR formula is annualized. To get the personal rate of return for the this time period, we have to adjust the annualized number. It’s easy enough to do. You just use this formula:

PRR (Personal Rate of Return) = [(1 + 3.148)# of days/365] – 1

To get the number of days you simply subtract 3/26/2010 from 4/16/2010 to get 21 days.

PRR (Personal Rate of Return) = [4.14821/365] – 1

PRR (Personal Rate of Return) = [4.148.0575] – 1

PRR (Personal Rate of Return) = [1.08524] – 1

PRR (Personal Rate of Return) = .08524 or 8.52% (different due to rounding)

In other words, his personal rate of return over that 21-dayperiod was 8.53% but if you annualize the number, it’s 314%.

Anyway, I hope this was helpful. The XIRR formula can be confusing.

Yesterday I used the RATE function in Excel for a post. A reader asked me if I could explain the RATE function. This post will attempt to do that.

First off, you use the RATE function when you want to calculate what rate of return would be required to meet a certain goal, based on a few assumptions. Let’s use the information from yesterday’s post:

Retirement Goal: $1,000,000
Years until retirement: 20 (240 months)
Current retirement account balance: $100,000
Monthly contribution amount: $500

You can set it up like this in Excel:

Excel RATE Function - 1

Then, to solve for the rate, you simply put your click on cell E1 and then choose Insert > Function from the menu. You should then see this:

Excel RATE Function - 2

If you don’t see the RATE function listed in the center menu, then you might need to select “All” from the drop down menu above and then scroll down in the main menu until you see RATE.

Once you click on RATE and then “Okay”, you’ll see a menu that looks like this:

Excel RATE Function - 3

Then, you simply fill in the information, referencing the appropriate cells like this:

Exel RATE Function - 4

Here’s a quick explanation of each input:

Nper – the number of periods involved in this example. It’s 20 years but we’re making monthly contributions, so it’s more accurate to use 240 months. So we reference cell C1 and multiply it by 12.

Pmt – the monthly contribution amount expressed as a negative number. Think of it like a cash flow amount. The $500 is flowing out each month. So, we reference cell D1 and multiply it by -1 to get a negative number.

Pv – the present value of the retirement account—again expressed as a negative number. We reference cell A1 and multiply it by -1.

Fv – the future value of the retirement account. This value is expressed as a positive number. We reference cell B1.

Type – input 1 if the payment is made at the beginning of the month or 0 or leave it blank if the payment is made at the end of the month. I chose the beginning of the month and therefore inserted 1.

Guess – (you’ll have to scroll down to see this input as it is located under Type) This function requires a rate guess in order work properly. The default is 10 percent. I left this blank.

After you enter all the necessary information, click “OK.” You should see .78%, although you might see 1%. In that case, simply go into cell format and change the number settings to two decimal places.

Also, the formula will give you a monthly return. To convert it to an annual number, simply convert the percentage to a decimal and raise that number to the 12th power. Like this:

(1 + .0078)12– 1

(1.0078)12– 1

1.09786 – 1

.09786 or 9.79%

Hopefully, if I have done a good job, you now know how to use Excel’s RATE function. If you have any questions, please feel free to leave a comment.

The following comment was left on this post from last week:

Have a question:

What reasonable standards should investors use to measure how well or poorly that they are doing?

I’m sure that an answer would include “it depends” but if so, depends on what?

We are about 10% under our 12.31.07 balances and we are pleased but how pleased should we be? There is always someone who well fare better or worse but I’m at a loss as to which reasonable “standards” that I should use to know how I’m doing?

That’s a very good question.

Unfortunately, the appropriate answer is: it depends.

From a general standpoint, your portfolio’s performance should be judged against the appropriate benchmark or benchmarks.

For instance, if you have a portfolio of 50% large-cap stocks and 50% bonds, you would not base your performance on solely on the S&P 500 Index. Rather, you’d base it on a 50/50 split between the S&P 500 Index and the appropriate bond index.

If your portfolio is comprised of large-cap, mid-cap, small-cap, bonds, and real-estate investment trusts, then you need to base the performance on benchmarks for all of those asset classes.

The reason for this is that it’s easy to say, “Wow! We did awesome last year. Our portfolio was up 8%!” The reality could be that a benchmark portfolio might have been up 12%, making your 8% return not so stellar.

Of course, another way to judge your performance is to do what BG suggested in the comments of that post and that is to base your performance on whether or not you’re meeting your future goals. It doesn’t matter how your portfolio is doing if it’s not helping you meet your future goals.

For example…

Let’s say you have a retirement goal of $1,000,000 (purely hypothetical, ignoring inflation). Your retirement is 20 years away and you have $100,000 saved up so far. You are contributing $500 per month into an S&P 500-based fund. You don’t expect your contribution amount to change (again, hypothetical).

Using the RATE function in Excel, I figured that the required rate of return to meet that goal is .78% per month (9.79% annualized). Given that the monthly geometric average total return on the S&P going all the way back to 1926 is .77% (9.64% annualized), you most likely will fall short of your goal by around $25,000.

This leaves you a few choices:

1. You can accept the lower amount at retirement.

2. You can take on more risk by moving into small cap stocks, which have a higher expected return but also are a lot more volatile (more on that in a future post).

3. You can increase your contributions. Based on my numbers, increasing the contribution amount to $540 per month, put’s the expected account value at a little over $1 million.

I realize that we are talking about math based on linear growth, which never happens in the real world. But, it can still be beneficial to have some sort of basis in reality. If your goal is $1 million and you’re investing a certain amount per month, it would be wise to know if you have a shot at meeting your goal.

Thoughts?

My church participated in Dave Ramsey’s “The Total Money Makeover Live!” event a couple of weeks ago. I did not attend the event but did pick up a copy of the workbook that went along with the event.

I have never counted myself among the Dave Ramsey fans. Sure, his advice is better than racking up lots of debt and not saving for the future. But, he also generalizes and has a one-size-fits-all approach to the advice he offers his listeners.

What bugs me most is the math behind his assumptions.

For example…

On page 3 of the above-mentioned workbook, is this:

The American Dream

Imagine if…
A 30-year old couple made $48,000 a year and saved 15% ($7,200 per year or $600 per month) in a 401(k) at 12% growth.

At 70 years old, they will have…
$7,058,863.50 in the 401(k)”

How did Dave arrive at that number? Here’s the math:

FV = $600 × (1 + .01)480

FV = $7,058,863.51

That’s a lot of money!

But, how would this look in the real world? I summarized Dave’s information into the following graphic and used 2009″s numbers from the IRS to calculate income taxes.

Dave's Math

For my example, I assumed that this couple does not have children. If that were the case, it would probably be possible for them to sock away $7,200 per year. Their budget would be tight unless they economized.

Then comes my next question:

WHERE ARE THEY GOING TO GET A 12% RATE OF RETURN FOR 40 YEARS?

Seriously, WHO assumes a 12% rate of return for 40 years? Later on in the book, Dave stresses diversification. There’s not a properly diversified portfolio on earth that is going to average a 12% rate of return on a consistent basis. The ONLY way you’re going to get that kind of return is to invest ALL YOUR MONEY in small cap stocks, which are highly volatile.

I think the word “imagine” was the proper word to use for his scenario because the only way he’s going to get those numbers is with IMAGINATION!

To bring us back to REALITY, I reran Dave’s numbers using a much more conservative .77% monthly rate of return, which happens to be the geometric average return for the S&P going back to 1926. Take a wild guess at what the 401(k)’s expected value becomes with that number?

$3,017,106

And that number’s even somewhat inflated because it assumes 100% of the money is invested in the S&P for all 40 years.

And…

Neither of those numbers include inflation, which would eat up at least half of those accounts.

So why does Dave use such a high number for an assumed rate of return? I would have to say it’s to give people hope (a false sense of hope, but hope nonetheless). When people look at those numbers, they go, “WOW! I can do that? I had no idea!” I will admit, that those numbers are eye-popping.

But,…

THEY AREN’T BASED IN REALITY

Thoughts?

I do a lot of stuff with Excel and I have learned some things that go slightly beyond the basics and make the program that much better to use. I still use Excel 2003 so this series of posts will be based on that version.

I got the idea for this post after putting together yesterday’s post about eating out.

In the Excel spreadsheet I used for that post, I had a list of dates along with the name of the restaurant and the amount spent like this:

Fig. 1

Fig. 1

As you can see, the list is in chronological order. I wanted to see how many times and how much we spent (the subject of another post) at each restaurant. I could have simply used the sort feature by highlighting the data and sorted them based on the restaurant name and then counted them that way.

I could have. But, there’s a better way…

Excel has a COUNTIF function that will count items based on the parameters you provide. It’s a really cool feature. I wanted Excel to count up the number of times we ate at each restaurant so that I could arrange it in a list insert it into yesterday’s post. To use the COUNTIF function, do this:

1. Figure out where you want to put your list. I decided to put my list in column D, E, F, and G like this:

Fig. 2

Fig. 2

2. Create the list of restaurants that you want to count. I used the data sort feature and sorted the data by restaurant name and then made the list from there.

3. Determine and click on where you want the COUNTIF function to put your information. For my list, it was column E (cell E2), which I titled “#”.

4. Then click on Insert on the menu tab and scroll down to Function:

Fig. 3

Fig. 3

You should be seeing a popup like this:

Fig. 4

Fig. 4

Where it asks for range, you’ll need to enter the range from where your information will come from. NOTE: You’ll notice the “$” around the column letters. This fixes the range so that you can copy and paste the formula for each of the restaurants in the list.

Then, where it asks for Criteria, you simply choose the cell with the restaurant name that you want to count. In this case it is cell D2, which contains “Buffalo Wild Wings Beaumont.” Then click on “Okay.” If everything is done properly, you should see the correct count of all the cells containing “Buffalo Wild Wings Beaumont,” which is 3 in my example.

5. Simply copy cell E2 and paste it in the rest of the D column until the list is complete. It should look something like this:

Fig. 5

Fig. 5

Next time I’ll show you how to use the SUMIF function.

Any questions? If so, leave a comment or send me an email and I’ll see if I can answer them.

According to the Vanguard website, the Vanguard S&P 500 Index Fund is down 12.07% YTD as of yesterday’s close. To get an idea of what that return would look like if it were to continue for an entire year, you can annualize the YTD return.

It’s a fairly simple calculation to perform as long as you have the following information:

1. Number of days that have elapsed so far this year. This is easy to calculate if you have access to Excel.

2. The YTD return of the investment that you want to annualize.

The formula for annualizing a ROR is pretty straight forward:

[(1 + YTD ROR)1/(#of days/365)] – 1

The YTD ROR should be expressed as a decimal. Plugging in the Vanguard S&P 500 Index Fund information from above, the equation looks like this:

[(1 – .1207)1/(204/365)] – 1

[.87931/(0.55890411)] – 1

[.87931.7892] – 1

0.7944 – 1

-.2056 or -20.56%

So, a 12.07% loss for the first 204 days of the year equates to a 20.56% loss on an annualized basis.

Now let’s say you are down 12.07% but you purchased this fund on December 31, 2006. How do you annualize that return? The only input that changes in the above formula is the number of days, which is now 570.

[(1 – .1207)1/(570/365)] – 1

[.87931/1.5616] – 1

[.87930.640350877] – 1

0.9209 – 1

-.0791 or -7.91%

Had you purchased an investment on December 31, 2006 that is currently down 12.07% since the time of purchase, your annualized rate of return on that investment would be -7.91%. Not much of a return is it? Anyway, now you know how to annualize your returns. Fun stuff!