Kmart Calculators, spreadsheets, and IRR's - Posted by David Alexander

import · November 12, 1999, 6:09pm

Posted by drew on November 15, 1999 at 07:31:08:

David,

Yep, that’s what I’m saying. 106% for IRR is way way way off.

There are two ways to set up your initial cashflow:

1)Use the cost of your initial cashflow (-7017.75) as your initial investment (occurring 1 month before you receive the first $500 payment) and have a cashflow of 0 for the first 15 months (this assumes the 500 coming in is immediately going out)

or

Just use the 500 going out as the cashflow for the first 15 months which ignores your intial cost of this cashflow

The first way is probably the best way to look at this problem since it takes into account the initial cost of your cashflow.

Using the first way I come up with an annual IRR of 28.73% and using the second way I get 31.73%. Either way, it doesn’t get anywhere near 100%.

I’d appreciate it if someone else would check my numbers on one of those fancy HP thingys to see if I’m in the ballpark.

-drew

import · November 12, 1999, 6:09pm

Kmart Calculators, spreadsheets, and IRR’s - Posted by David Alexander

Posted by David Alexander on November 12, 1999 at 19:09:06:

Ok, since most of you know I’m pretty particular about my little TI BA calc, but of course as always it doesnt do everything I need. One of these days I’m going to step and buy a REAL Calc but until then.

How do I figure the rate of return on buying a cashflow with a cashflow, using excel.

What built in formula’s in Excel do this for you.

Example opportunity to buy a cashflow for 700/month for 36 months in exchange 1200/month for 18 months, of course I’ll use 700 of the original cashflow plus an extra 500 of mine.

How do I compute my return on this using my excel spreadsheet, base the PV’s at 10% on both cash flows

Thanks,

David Alexander

import · December 30, 1999, 6:41pm

Re: Spend thousands of hours learning Excel obtain a BBA in Finance and Real Estate. - Posted by Harvey Carroll, Jr.

Posted by Harvey Carroll, Jr. on December 30, 1999 at 19:41:26:

Excel is tough. I have developed some rough spreadsheets that sort of go the way you are talking about. I would be interested in trading for something that you think would help me; however, I forwarn you that you would have to spend a great deal of time on it and you would have to know a lot of algebra to work with the spreadsheets.
JR

import · November 15, 1999, 4:56pm

22.64% - good enuf. - Posted by John Behle

Posted by John Behle on November 15, 1999 at 17:56:57:

Sounds good. An excellent reinvestment. And keep in mind that it’s a true 22.64% (remember how I went into how IRR is a “flawed” calculation).

The trusty spreadsheet came up with the same answer as Drew, but I didn’t check it on the HP. What’s your abacus say? If you want, for a few bucks I’ll sell you some “Elwood” tables.

import · November 15, 1999, 3:29pm

Before re-inventing the wheel … or in your case… - Posted by John Behle

Posted by John Behle on November 15, 1999 at 16:29:55:

chiseling a rock into a round shape

For whatever reason, I was frustrated not being able to do uneven cash flows on the spreadsheet easily, so I took a few minutes and put together an easy spreadsheet that handles up to ten cash flows of any length and calculates them immediately at 8 different yields. It works very smoothly in Quattro and should translate seemlessly into Excel, but I will test it first.

import · November 15, 1999, 3:22pm

First Pull out your sun dial to check the time… - Posted by John Behle

Posted by John Behle on November 15, 1999 at 16:22:51:

… then pull out your abacus. Take some rocks and rub them together to create fire. Char the end of a small stick. Find some papyrus…

Or spend a buck or two.

Actually, I’m just putting the finishing touches on a Quattro Pro spreadsheet that will handle the uneven cash flows. I can water it down so it will run on Excel and then I’ll make it available for you.

Unless someone wants to take it as is - fully functional, but not beautiful and add some bells and whistles for everyone to use.

By the way, have you moved up to a 286 yet??? They’re much faster than your XT.

And as Drew pointed out - thanks Drew - you can do it on a TI if you break down the individual cash flows like we went into at the bootcamp. Unfortunately, you have to play trial and error to come up with your exact IRR. Just keep trying different yields until you hit close to zero on the NPV.

Send me a letter by pony express when you’re done.

import · November 13, 1999, 6:13pm

IRR and NPV using Excel - Posted by drew

Posted by drew on November 13, 1999 at 19:13:00:

Hi again David,

I’m assuming you have the Excel for Windows95 or higher, if not these instructions may not hold. Most of what I’ll describe below can be accessed by using the “fx” icon in Excel and then finding IRR or NPV in the Financial functions menus.

Ok, to calculate NPV or IRR in Excel you first have to set up the cash flows in a column. Using your example, put eighteen -500’s directly followed by eighteen 700’s into the same column. Let’s assume that you put these in the first column (A) and in the first 36 rows.

Put your cursor in the cell at the bottom of the column (you can actually do this anywhere in the spreadsheet, but it helps if you end up highlighting the series). Type “=IRR(A1:A36,.01)” and hit return. The “A1:A36” refers to the cells where the cash flows reside and the “.01” is just a guess at what the monthly IRR should be. You may have to play around with the guess some if you get an error.

Using the above function you should get an answer of 1.887% for the monthly IRR (22.64% annually).

To find NPV go to the cell below the one where you just calculated IRR. Type “=NPV(0.1/12,A1:A36)” and hit return. The “0.1/12” is the monthly discount rate and the “A1:A36” refers to the cells containing the cash flows. You should get $1,712.87.

Hey, whattaya know, same answer as the TI BA gave us.

Since IRR is the discount rate at which the NPV equals zero, you can put our number for IRR (1.887%) into the NPV function, which should then return zero for the NPV. Just a way of checking your answer if the almight TI BA is on the fritz.

Again, I hope this explanation is clear enough to follow along. If not, let me know and I’ll email a sample spreadsheet to you.

-drew

import · November 13, 1999, 5:42pm

Actually, you can use the TI (LONG) - Posted by drew

Posted by drew on November 13, 1999 at 18:42:45:

Hi David,

First of all, don’t let John and all of the other HP zealots fool ya, you can turn lots of nifty tricks with the TI BA’s without carrying around a 5-pound manual or wearing a pocket protector. I’ll put the Excel explanation for IRR and NPV in my next post. Anyway, here’s how to do it on your calculator:

You are actually finding the NPV of the cashflow discounted at 10% per year. First let’s deal with the initial cash flow of -500 (700-1200)per month for 18 months

PMT = -500
N = 18
%I = 10/12 = .8333
FV = 0
Compute PV and you should get -8325.41

Next is the 700 per month you will receive for 18 months, starting 18 months from now. Initially you must figure out what the PV of those payment is 18 months from now and then dicount that value back to the present:

PMT = 700
N = 18
%I = 10/12 = .8333
FV = 0
Compute PV to equal 11,655.58

This is the value of the cash flow 18 months from now, but we want to know what it is worth today so use it (11,655.58) as the FV:

FV = 11,655.58
N = 18
%I = 10/12 = .8333
PMT = 0
Calculate PV to equal 10,038.29

So your NPV is 10,038.29 - 8,325.41 = 1,712.88
(assuming a monthly discount rate of 10/12 percent throughout)

Given that the NPV is positive using a 10% discount rate, we know that the IRR is greater than 10%, but there is no easy way to figure it out using the TI BA series calculator. I’ll explain how to do it in Excel in the next post.

Hope this helps.

-Drew

import · November 15, 1999, 7:30pm

LOL… - Posted by Tyler

Posted by Tyler on November 15, 1999 at 20:30:00:

Think how many deals he could do in the amount of time it takes that thing to power up!

Sounds like the ROI on a new calc would be in the “good enuf” range…at least.

NT

import · January 18, 2001, 5:03am

Re: IRR and NPV using Excel - Posted by Mirela Hadziomerovic

Posted by Mirela Hadziomerovic on January 18, 2001 at 06:03:17:

Please can you send to me a sample spreadsheet for NPV and IRR calculation.

import · July 17, 2000, 6:22am

Re: IRR and NPV using Excel - Posted by Siva Kumar Neelam

Posted by Siva Kumar Neelam on July 17, 2000 at 06:22:55:

hai,
ihave seen ur explanation regarding usage of MS Excel to calculate NPV and IRR.
Actually i want to write a program on it for our firm purpose .can u tell me the formulae indulged in it to calculate them.
if possible pls mail it to my mail address
thank Q
siva

import · November 14, 1999, 2:42pm

What I did… - Posted by David Alexander

Posted by David Alexander on November 14, 1999 at 15:42:41:

Was compute the numbers just like you did except that I
Took the present value of the first series put it into present value

PV 8325.41 , N 18, 0 PMT, FV 11655.78, compute for I% and got 22.64% in my calc.

I havent set up the spreadsheet yet but will in the next day or so, thank you for your help.

David Alexander

import · November 15, 1999, 11:27pm

If I bought a calc… - Posted by David Alexander

Posted by David Alexander on November 16, 1999 at 24:27:20:

that actually figured out all those complicated yields I might not ever find the kind of deals I find, LOL,

See ya Wednesday, thank you my friend.

David Alexander

import · November 14, 1999, 7:01pm

Ugh, I hate it when that happens… - Posted by drew

Posted by drew on November 14, 1999 at 20:01:10:

Hello again David,

You’re right in this case, but ONLY because the cash flows are consistent (all -500 and then all 700) AND occur for the same consecutive time periods (18). You’ll even get the same answer if you just put in PV=500, FV=700, N=18, PMT=0, and solve for I. My friend, you have discovered the exception to the rule (geez, there’s one in every class!).

When solving for IRR you do not discount the cash flows at all since calculating the IRR is actually the finding the discount rate at which the NPV would equal zero. Regardless of what discount rate you used in this case(you used 10% in your calcs, but could have used 0%, 32.44%, 50%, etc… and have gotten the same answer) your ratio of FV to PV would equal 1.4 because of the reasons mentioned above and would therefore provide an annual IRR of 22.64% over that 18 month period.

Just to illustrate what I’m rambling on about, drop off the last payment of 700 so that you only receive 17 payments of 700. Now do your calculation again.
You’ll get a monthly I of 1.587% monthly or 19.04% annually. But alas, the real IRR is 1.607% monthly or 19.28% annually. Playing around with the spreadsheet will shed more light on the sensitivities of IRR.

-drew

import · November 14, 1999, 10:35pm

So what your saying… - Posted by David Alexander

Posted by David Alexander on November 14, 1999 at 23:35:20:

is that the way I computed this only worked on these particular cash flows and the same formula I used will not generate a true IRR or return on my buying cash Flow in other scenarios.

The actual scenario I’m looking at doing is to buy my way into some newly creating mortgages using some existing cashflow.

The existing cashflow I’m looking at using is returning me 10.11% so it would be as follows.

15 N, 10.11 I%, 500 PMT, 7017.75 PV

In return I will get 239.65 on some newly created mortgages for the remaining 345 of the 360 months.

So, that would be as follows:

345 N, 11 I%, 239.65 PMT, 25021.19 PV

then I entered

25021.19 FV, 0 PMT, 15 N, 7017.75 PV, and calculated for I%, which came out to 106.14%

Is this not correct?

Thanks,

David Alexander