My FCFE DCF Spreadsheet

[galumay]

Well its pretty messy, but its a work in progress and it does do a 2 stage DCF and you can vary growth rates, discount rates and confidence rates. You can also use FCF, FCFE, EPS or any number you want!

[galumay]

I have made a lot of changes over the years, I will try to make another downloadable copy. I have now included a estimation of what is assumed to be Buffett's model for a quick & dirty valuation - FCF/RFR.

The FCF calculation is based on NOPAT which I have used EBIT as a proxy in Australian terms, then subtracted CapEx, Delta in Working Capital, and Interest. The Risk Free Rate I have used is 6%.

[galumay]

I have developed a feed for prices into excel, its saved in a .iqy file stored in the path of User/username/Library/Group Containers/UBF8T346G9.Office/User Content/Queries/

The file has one line of code at the top, in my case to query for my holdings it is,

https://download.finance.yahoo.com/d/quotes.csv?s=ADA.AX AHZ.AX BRG.AX CCP.AX CDA.AX DDR.AX ICU.AX ITD.AX KME.AX KPT.AX MMS.AX MND.AX MOC.AX MXI.AX MYX.AX NWH.AX NVT.AX RCG.AX REH.AX RFG.AX SDI.AX SGH.AX SIG.AX SND.AX SRV.AX SXE.AX TGA.AX TGR.AX TNE.AX UOS.AX VOC.AX VRS.AX WPL.AX WPP.AX&f=l1

Then we need to create a new worksheet in the spreadsheet, I named it PRICES, then click on "Data" in the menu bar, and click on "Get External Data" and "Run External Query.." It will go to the Queries folder and you can select the .iqy file to load it, leave it on "Existing Sheet" and "=$A$1", click OK and it will load the prices.

You can then create references in other sheets to the live price. If you need to change anything you have to delete the settings, delete the PRICES sheet and then create a new sheet, rename it PRICES and link the data again.

[galumay]

I no longer use anything like this spreadsheet, and I really wouldn't recommend downloading it or using it!

The spreadsheet I use now for my primary research uses Quick FS as a data source and I use it to look at ROIIC, Reinvestment rate, compounding rate, FCF yield, a really simple proxy for DCF valuation that has all the metrics reverse engineered - so I can see what assumptions are baked in the current price - RoR, FCF, Growth.