Calculate %Interest , Type Mismatch Problem

[awsmitty]

I am trying to calculate the interest on stock returns using:
=((POWER(Report[Total Value]/Report[Book Value],Report[x]))-1)*365,
where x = 1/(1.*(TODAY()-Report[First Trade Date]))
I am fairly certain that the equation is correct. I keep
getting an error: Results are too large or type mismatch
My hunch is that it has to do with the dates in the POWER function,
but that's a guess. Can someone help, please.
Thanks

 

[Glove_Man]

I think you are making a mistake in the section you have labelled as x. You seem to be assuming the return should be calculated Daily then changed to yearly by multiplying by 365.... I don't think that's true.

What do you mean "the interest on stock returns"? That could mean a bunch of different things.

Perhaps try this:
=100*(POWER((Total Value/ Book Value),365/(TODAY()-Trade Date))-1)

 

[awsmitty]

Hummm, interest, like interest at the bank. If I bought a stock on day 1 for $10 and 30 days later it grew to be worth $50, what kind of interest would that be? If I opened a savings account at the bank with $10, what interest would the bank be paying if that $10 grew to $50 in 30 days? Same thing. I hope that explains interest. I tried your equation. It doesn't work. Not even close. The reason I tried the x value above was to try and convert the date type to a number, hence that's why it is multiplied by 1. The equation could easily be =((POWER(Report[Total Value]/Report[Book Value],1/(1.*(TODAY()-Report[First Trade Date]))-1)*365. The exponent in this equation is the number of compounds, not what fraction of a year it is.

 

[Glove_Man]

I work at a bank. In the the interest rate department. There are lots of different ways to express the interest rate. I am well aware that the exponent is the number of compounds, not always what fraction of a year, but since you are using the subtraction of two dates you will be getting an integer number of days, I assumed you were aiming for a daily compounding formula.

The formula I gave you above is the way to calculate the effective annual return. Try it with a more placid example. For example, one year ago you bought a stock for $100 and today it is worth $107. Quite clearly the return is 7%. And, presto, that's what my formula gives.

If you want to get the return rate compounded daily, then you can use this:

=365*100*((Today's Price/ Purchase Price)^(1/(TODAY()-Purchase Date))-1)

Try that with the 100 to 107 example over one year. You'll see the answer is 6.77%. That's because the compound interest helps the return to be slightly higher than the stated interest rate.

 

[awsmitty]

Well, we are now using the same equation, pretty much; you are multiplying by 100, I am not. True, if you take either equation, put it in a given cell within excel, reference real data, you get the correct results. You can also use the RATE function in excel and get the same results. The problem arises when the equation is placed into PowerPivot. This is why I am using the POWER function and not " ^ " (PowerPivot doesn't like " ^ ", or at least initially I thought this was a problem) and the syntax Report[ ]. When I attempt this I get the following error: ERROR - CALCULATION ABORTED: Calculation error in measure 'Report'[APR]: An argument of function 'POWER' has the wrong data type or the result is too large or too small.

We know from placing the equation into excel that the number is neither too large nor too small. So, I conclude I have a TYPE Mismatch problem. I have tried every type that could possibly be appropriate, same error. This is also why I took the exponent out of the POWER function and replaced it with "x" (see initial post). I have tried it both ways, leaving the dates in the exponential, and using "x". Same error.

Another note, I and new to PowerPivot, very new, been using it now for about 2 months.

 

[awsmitty]

Glove_Man

I owe you an apology.

I figured out the problem. As mentioned in the first post, I was using Book Value and Total Value, which reflects the total cost when the stocks are bought and the total amount received when sold. If I use Book Price and Today's Price (the price of one share, which is the same ratio, the Value vs. Price differ only in the number of stocks bought), then all works out perfectly. So, it was neither a type issue nor the equation. I should also note that to get the equation to match perfectly with the excel RATE function, I had to add one day in the exponent of the POWER function.

Thanks for your help in trying to solve the issue

 

[Glove_Man]

Ahhh - that would explain it. Don't mention it.

Glad you got it sorted