Made some improvements over the weekend and re-uploaded the Excel.
Changelog
- Fixed the formula for Terminal Year Discounting logic
Now, Terminal Year Discounting is capped at Risk-free Rate*1.50. All companies’ Cost of Capital usually converges on ‘a little above’ the Risk-free Rate (This is common across all countries). Even if you are considering using your Opportunity Cost as the Discounting Rate, it’d be very difficult for your to earn more than a ‘little above the Risk Free Rate’ as time progresses. Your investments would simply get too big. I read several articles and research papers before committing to this change. Incidentally, this coincides with @Yogesh_s ji’s logic of allowing for lesser Risk in the Terminal Year (Although I still don’t believe in the CAPM mumbo-jumbo).
- Added entries and formulas to calculate the Value of outstanding Stock Options using the Black-Scholes-Merton Option Pricing Model
This was long due. I was putting this off because of the sheer amount of careful inputs required. A wrong bracket and the entire formula could return a very wrong number. messing up the Value. It was especially difficult because I had to contain the entire length of the BSM Option Pricing Model into a single cell. Just for fun, this is how the formula looks like:
=IFERROR((((NORMSDIST(LN((H2/B12)/F12)+(B11+((J12^2)/2))/(((J12^2)^0.5)(H12^0.5))))(H2/B12))-((EXP((-B11)*H12))*F12)*D12),0)
- Added attribution text at the end
I also added an attribution text at the end, recognizing Prof. Aswath Damodaran as the inspiration for building and improvising this Valuation tool.