So I built a (simple) model in Argus of a master leased property with a specific expiration date.
Under Yield>Present Value Discounting, I set Primary Discount Rate to 9%, netting a Total Present Value of ~$17.4MM. When I export the Schedule of Cash Flow from Operations report to excel and enter an NPV function there, I get a value of ~$16.8MM.
Why do Argus and Excel differ? Differences in calculation methods?
I tend to notice a difference as well. Not sure what causes it, maybe argus and excel round numbers differently, and those small changes add up over time.
Annual versus monthly?
Are you using XNPV?
The annual versus monthly could be it. Try this: Annual Hurdle Rate = 15% Monthly=(1+15%)^(1/12)-1 Monthly =1.17%
Let us know if its something else, I'd be curious to know b/c I've noticed small variances occasionally.
Just used XNPV (I'm assuming correctly), and yielded an entirely new result. $18.3MM.
Keep in mind I'm valuing a leasehold, so the cashflows just end. Can you explain annual versus monthly a bit further?
The discount rate needs to be adjusted when compounding monthly vs the annual rate.
The Excel NPV function is an old creep that does not go away until Microsoft decides to introduce new financial functions in Excel 2015 or 2016 that are based on other existing financial functions
The Excel NPV function finds net present value where the first cash flow is discounted at time period t=1
I am not an Argus user so not sure what sort of NPV function does Argus has
When using XNPV Excel function, the first cash flow gets discounted at time period t=0 thus it would only confirm that your Excel NPV calculations of 16.8MM * (1.09) = 18.3MM where the (1.09) is the interest factor (1+i) that converts ordinary annuity into an annuity due
As others have stated NPV calculations are quite complex. I will give brief introduction to what is required without leaving any links to web sites as that might be taken as SPAM and this is my first post thus do not like to called a SPAMMER
NPV is simply the sum of discounted cash flows. Now the annuity payments may be in constant amounts in which case we use a closed form formula for ordinary annuity and annuity due. For investments with uneven amounts, the long form sum of discounts is applied in finding NPV
Issues that need to be addressed contain the following
1) Compounding frequency of interest (Annual, semi-annual, quarterly, monthly, fortnightly, weekly, daily, infinite (continuous), or biennial, triennial or anything else.
2) Length of the payment period (Year, half-year, quarter, month, fortnight, week, day, biennial, triennial, or anything else)
3) Discounting convention. They teach full-year discounting at school when finding NPV. On Wall Street they use mid-year discounting but there are many other discounting conventions such as 1st quarter, 3rd quarter, biennial, year and half and anything else
4) Day count convention. In NPV calculations, 30/360 day count convention is used whereas there may be other day counts that are applied such as Actual/360, Actual/Actual, 30/365, 30/Actual and so on
5) Tax applies on income so NPV calculation should address the tax burden in finding net present value
6) Income expected in future is prone to risks and to adjust for possible losses the NPV calculations should allow for possible haircuts on future earnings
7) And for those who con others to make a quick buck, NPV calculations should allow for rigging the discount rate that results in lower than actual NPV
All these features are readily available in 3rd party financial functions.
Thanks for such a thorough response, AbrahamA
I worked around the issue by setting the Argus model to end exactly 35 years out vs the 34 and 4mos or so that I had previously. This changed the first column to a full year instead of a fractional one. By using a straight NPV formula, I was telling Excel to see that first fractional year as a full year, which I'm sure threw off the result quite a bit as it shifted every subsequent year forward in terms of discounting for NPV.
It would be nice if Argus would output that fractional year in a report at the end instead of the first year-- would make running return calculations a lot easier.
Thanks for your help everyone!
If the duration of investment was 34 years and 4 months then you should have used such a duration rather than rounding out to 35 years
There are 3rd party Excel NPV functions such as tadNPV that would have produced the same NPV result as that of Argus
Repellat dignissimos est et earum officia. Minima aut soluta qui ullam eum. Cumque dolor et omnis unde expedita id architecto laborum.
Ipsa dolor porro eos. Dolores et quo omnis animi iure. Doloremque dolorum odio aspernatur soluta. Sit consequatur reprehenderit aut deserunt.
Reprehenderit animi eius aut magni. Magnam consequuntur officia quo cum aspernatur. Beatae maxime explicabo sequi dolor. Odit et autem nesciunt quae ut quidem officia est. Perferendis labore dignissimos delectus officiis.
See All Comments - 100% Free
WSO depends on everyone being able to pitch in when they know something. Unlock with your email and get bonus: 6 financial modeling lessons free ($199 value)
or Unlock with your social account...