North American Financial 15 Split Corp T.FFN

Feedback on my dashboard/spreadsheet

[maxjac]

Here's what my spreadsheet looks like. Please note it is a WORK IN PROGRESS and it is not meant to be ultra precise.

 

Here is my logic:

 

As of July 15, I calculated the portfolio value based on the July 15 NAV reported by FFN on their website which is $16.14 as of July 15.

 

I took $16.14 and then took $10 from that (which is the base value of each Preferred Share) and multiplied by the shares outstanding (53,833,192) for Preferred Shares as of July 15. I get $538,331,920.
 

Then, I took the balance of $6.14 (which is the base value of each Class A share as of July 15 closing) and multiplied by the shares outstanding (52,890,992) for Class A shares as of July 15. I get $324,750,691.

 

Total portfolio value is $538,331,920 + $324,750,691 so $863,082,611 total portfolio value and this includes stock AND cash.

 

I then calculated the cash position as 6% of the total portfolio value because that's usually what FFN has. 6% of $863,082,611 is $51,784,957 of cash which means that the balance is in stock so $863,082,611 minus $51,784,957 of cash is $811,297,654 in stock.

 

To recap, I ended up with a total portfolio value of $863,082,611 of which $811,297,654 is stock and $51,784,957 is cash.

 

I took the total portfolio value of $863,082,611 and applied the percentages (weighting) as indicated in the latest quarterly report from February 28 (yes, they've probably changed since but that's all we have available to work with).

 

That gave me the total $ investment for each stock and from there, I calculated the number of shares owned for each stock based on the closing value for each stock as of July 15.

 

For example, the weighting for Royal Bank as of the February 28 quarterly update was 8.1% of the total portfolio value (please note I used 8.13% for Royal Bank - I adjusted hundreds of percentages all around to balance out the CAD vs US holdings).
 

Okay, back to the Royal Bank example... So that's 8.13% of $863,082,611 which is $70,168,616.

At closing on July 15, Royal Bank stock was worth $151.51 CAD.

So, to figure out how many units of the Royal Bank stock FFN owns, we take $70,168,616 and divide by $151.51 to get 463,129 shares.

 

Now that we have the number of shares for each stock in the portfolio, I'm simply updating the spreasheet with LIVE market data and that is what's giving me the daily NAV.

 

Thoughts?

 

Max.

 

Here's my dashboard as of closing on July 15:

 

 

Here's my dasboard as of closing on July 19:

 

[Splitfunding]

Very nice work there, its clean, clear and well organized. I like how you track the ex-dividen of the holdings as well. That's something I feel like not everyone does - I mean I don't do it myself in my own more rudimentary chart as I'm just trying to get estimates of the price action and NAV movement. But I understand its there an adds value and definitely should be tracked for those

[maxjac]

Thanks for the feedback @Splitfunding much appreciated. I'll run this for a few weeks see how it matches up to the official FFN numbers. If it turns out to be accurate then I'll see how I can make this into a public webpage so everyone can benefit from it. That's for the future. Any other feedback or corrections / suggestions is welcome!

[alder]

Many thanks, maxjac. Very good of you ! Alder