›

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Sign In Register

Quick Links

Categories

Calculation of Profit %

rmweiss12rmweiss12
in Excel Workbook
Hi - What is the formula for calculating the Profit% in the Holdings tab?

Another question: If I wanted to track Unrealized Cap Gain% and Profit % for T (AT&T) by year from the time of first purchase, how do I generate that report? Not using Power BI Desktop. If I just look at the most recent year 2021 and last month (Feb) I see -6.29% for Unrealized Cap Gain% and -2.6% for Profit %. Are these the right numbers to look at?

I guess that Unrealized Capital Gains % is cumulative over time and is based on the stock price but what about Profit% which includes dividends paid?
Please clarify. Thanks.

Comments

  • VidasMatelisVidasMatelis
    Here are some simple formulas, even they are in DAX, they are easy to understand: 
    
Value Change %:=[Profit %] - [Dividends %]

Unrlzd Cap Gain %:=ROUND(DIVIDE([Unrlzd Cap Gain],  [Cost Basis]), 5)

Dividends %:=var tr = CALCULATETABLE(Transactions, TransType[DividendFlag]=1)
 RETURN IF(NOT(ISEMPTY(tr)), var res = PRODUCTX(CALCULATETABLE(VALUES(Dates[Month]), tr), 1+DIVIDE([Dividends], [Total Value]))-1 RETURN IF(res=-1 || ROUND(res,5)=0, Blank(), res) )
    I will explain Dividends % as it is a bit more complicated. We calculate Dividends % monthly, get amounts of dividends paid and divide that value by total value at the end of the month and then "geometrically link" results.
    Example:
    Jan 20$ dividends for 1000$ holdings (2%)
    Feb 30$ dividends for 900$ holdings (3.333%)
    Formula:
    ( (1 + 20/1000) * (1+30/900) ) - 1 = 1.02 * 1.033333 - 1 = 0.054 = 5.4%


    Profit % formula is much more complicated and actually very much depends if Cash * symbol is included in the calculation. You can review that formula in PowerPivot - ALL formulas in PowerPivot are "open" - visible to everyone.
    The problem with Profit % is that it is an approximate calculation, that works best for short time periods when there are not too many "movements". Larger deposits/withdrawals, or larger purchases skew results. That is why in the latest release Profit % is not by default included in Yearly or Holding reports.
    Proper way to check your performance is to look at "Annlzd RoR" measure - it is properly Microsoft implemented XIRR function (google Excel XIRR). Problem with this calculation is that results are hard to understand for short periods. If you would look at Annlzd RoR for a single month, that would show you your annual profit/loss % IF returns would be exactly the same for the other 11 months.
    So for short periods Profit % is OK, but for anything above 1 year you should not be looking at Profit %, but should be looking at "Annlzd RoR". And just to confirm - "Annlzd RoR" returns include dividends %.
Sign In or Register to comment.