Net Present Value problem

[spacecaptainsuperguy]

I'm trying to set up a "simple" calculator for a co-worker who sells memberships at a country club. The purpose of the calculator is to help show an individual why it makes financial sense to join now (mid-year) and have monthly dues waived for some number of months as opposed to waiting until December and joining right before the membership deposit goes up in January and be required to pay dues from day one.

I'm not exactly sure how to build an NPV formula for the 'join later' column (cell C11). The assumption I am using is that the prospective member would take his money and invest it at the assumed rate and then use those funds to join and pay dues at the later dates. I've got a formula in there right now, but it is only giving me their net out of pocket expense, not the net present value of those funds.

If it is easier for you, you are welcome to download the spreadsheet at:
http://www.4shared.com/dir/7676441/8cca2992/sharing.html
otherwise, here is what I have so far:

...and thanks in advance for any help.
SpaceCaptainSuperGuy

NPV Calculator.xls
	A	B	C	D
1	CurrentMembershipDeposit	27,500
2	ExpectedNewMembershipDeposit	30,000
3	CurrentMonthlyDues	360
4	ExpectedNewMonthlyDues	390
5	AssumedInterestRateEarned	8%
6	NoofMonthsDuesWaived	24
7	CurrentDate	7/29/2008
8	JoinLaterDate	12/31/2008
9
10		JoinNow	JoinLater
11
12	PresentValue	27,500	33,485
13
14	July-08	(27,500)	34,910	Interestearned
15	August-08	0	0	233
16	September-08	0	0	233
17	October-08	0	0	233
18	November-08	0	0	233
19	December-08	0	(27,500)	49
20	January-09	0	(390)	47
21	February-09	0	(390)	44
22	March-09	0	(390)	42
23	April-09	0	(390)	39
24	May-09	0	(390)	36
25	June-09	0	(390)	34
26	July-09	0	(390)	31
27	August-09	0	(390)	29
Sheet1

 

[lalbatros]

First calculate the monthly interrest rate = (1+8%)^(1/12)-1 = 0.643% .
Let's assume this value is in cell C5.
Enter the NPV formula: =NPV(C5,C13:C37)

 

[spacecaptainsuperguy]

That gives me an NPV of $1,533 which still does not look correct. Did you get a different answer?

SpaceCap

 

[Kaps_mr2]

I think the formulae should be = C13+(C5,C14:C37) as C13 is at time 0 and doesb't need to be discounted. Thanks

Kaps

 

[lalbatros]

scsg,

The value should be correct, but we have to know the conventions and see if they match our expectations.

Let's take the column "Join Now" first.
If you calculate the NPV in the same way, you get -27324.
This may look strange since you may have expected -27000 instead.
This is so because the NPV calculates the discounted values on the period just before the first period in the list. If you prefer to define the "present" as the first row in your list instead of the period just before, then you have to multiply the result by (1+r) = 1.00643. This gives: -27324*1.00643 = -27000 of course.

For the column "Join later", the same correction gives: 1533*1.00643 = 1543.
This value makes sense to me as far as I understood the story.
Instead of paying 27500 in 200807, you pay this amount in 200812 and 390 during 19 month thereafter. Thanks to the delay, you put the total expense (34910) on a bank account with an intereset rate of 8%/yr. The benefit of doing so is 1543, expressed in preent value at month 200807. This means that you better wait december.

I could not resist to calculate how much should the 19 monthly payements be for the benefit to vanish. You can check that this is 501 instead of 390. You can also make a curve of that. This looks all very logical.

Finally, I also checked the result returned by NPV, by discounting each cell of the "Join later" column, back to the appropriate "present". I got axactly the same result.

I have used NPV, IRR and these things quite a lot in the past. I find that always very funny.

 

[spacecaptainsuperguy]

I don't use NPV very often, but the part that is stumping me...especially since you are saying that $1,543 looks correct...is this:

The intention of the spreadsheet is to show someone that it makes financial sense to join now. What I'm wanting to show in the join later column (cell C12) is that you will need this much money right now earning 8% (or whatever percent is chosen) in order to break even with the deal I'm offering to you if you join now. If $1,543 is in fact correct, that is saying to me that I need $1,543 dollars right now in order to have enough money to pay the $34,910 that I will be required to pay out over the course of the next 24 (or some number of) months, which is obviously wrong unless the $1,543 needs to be subtracted from the total out of pocket (the $34,910).

And now that I think about it...that may in fact be correct since the amount earned that I show in the interest earned column is very near to that amount.

What are your thoughts?
...and continued thanks.

 

[xenou]

I think for the purpose of comparison, you must assume the buyer has the cash available from Day 1, regardless of the plan (otherwise, for instance, there's the question of how much interest he is earning - might be less if he only has 27,500 in the bank right now...)

So from a NPV perspective, the NPV of both option is the same. The question becomes, how much could he earn on interest (FV?)...I'm confused at this point. However, I think its clearly a better deal to buy now...you really lose a lot with those $390 payments, and I don't care how much interest you earn, you're worse off...

Does this way of presenting the options help? The final column in each group is cash in the bank...the interest earned is a simple formula on one periods earnings, which is added to the beginning balance of the next period.

I've left out some middle rows to save kb's.

AB

NVP-Sample.xls
	A	B	C	D	E	F	G	H	I	J
1		BeginBal.	Interest	Pmt	EndingBal.		BeginBal.	Interest	Pmt	EndingBal.
2	07/31/08	34,910	233	-27,500	7,643		34,910	234	0	35,144
3	08/31/08	7,643	51	0	7,694		35,144	235	0	35,379
4	09/30/08	7,694	51	0	7,745		35,379	237	0	35,616
5	10/31/08	7,745	52	0	7,797		35,616	238	0	35,854
6	11/30/08	7,797	52	0	7,849		35,854	240	0	36,094
7	12/31/08	7,849	52	0	7,901		36,094	242	0	36,336
8	01/31/09	7,901	53	0	7,954		36,336	243	-27,500	9,079
9	02/28/09	7,954	53	0	8,007		9,079	62	-390	8,751
10	03/31/09	8,007	53	0	8,060		8,751	59	-390	8,420
11		-	-	-	-		-	-	-	-
12	06/30/10	8,846	59	0	8,905		3,553	25	-390	3,188
13	07/31/10	8,905	59	0	8,964		3,188	22	-390	2,820
14
15				CashinBank7/31/10:	$8,964				CashinBank7/31/10:	$2,820
Sheet1 (2)

 

[xenou]

So from a NPV perspective, the NPV of both option is the same.

What I mean, is ... he has $34,910 now.

Perhaps you could work out a PV on the series of unequal cash flows based on the payments and interest earnings in the table, but in the end I think the most important thing is the final balances on what you've spent...it's clearly costing more to use the second option, regardless of whether it is discounted to present value or not!

 

[spacecaptainsuperguy]

Thanks Alexander. I think your approach is a good one in that showing an individual what they would save by joining sooner rather than later was at the heart of what I was trying to accomplish with this thing.

If anyone cares, you can download the final product at: http://www.4shared.com/file/57292959/3d16ce00/NPV_Calculator__Final_.html

I set it up so you can approach it from two different angles. One is by inputing a rate of return (i.e. the prospect mentions that he could just keep his money invested at X%) which will show them how much more that route will cost them. The other angle is to push the break even button which runs a macro for the Goal Seek which shows them what rate of return they would need on their money to break even with the deal being offered to them.

Again, thank you to everyone who helped. As always, I know where to come to get the help and ideas I need.

SpaceCaptainSuperGuy

 

[lalbatros]

scsg,

I thought you might enjoy a further comment.
You can find the "break even" without goal seeking. The IRR just returns what you want.

Indeed, you can check that for example:

NPV(35% converted to month, {0,0,0,0,-27500,-390,-390,-390,-390,-390,-390,-390}) = -26450

and conversly,

IRR({26450,0,0,0,0,-27500,-390,-390,-390,-390,-390,-390,-390}) = 35% converted to month