Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating Annual Return From One Time Payment
Let's say you have an investment that costs $X on Date A and on Date B it
pays $Y and you get back your principal. How do you calculate the annual rate of return for this simple investment in Excel? Assume that I already calculated the one time payment as a present value, based on the value at the start date of the investment. -- W |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating Annual Return From One Time Payment
On Jan 24, 2:49*pm, "W" wrote:
Let's say you have an investment that costs $X on Date A and on Date B it pays $Y and you get back your principal. How do you calculate the annual rate of return for this simple investment in Excel? There are several ways to state the rate of return. The simple annual return is: =(X+Y)/X / ((B-A)/365) Note: For investments in traded securities, it is not uncommon to use 252 instead of 365 -- 252 being the typical number trading days per year. But that also requires that you determine the number of trading days between A and B, which is non-trivial. The compound annual rate of return (aka CAGR, compound annual growth rate) is any of the following equivalent formulas: =(X+Y)/X ^ (365/(B-A)) =rate(365/(B-A), 0, -X, X+Y) =(1 + rate(B-A, 0, -X, X+Y))^365 - 1 (But sometimes RATE requires a "guess" argument in order to return a valid result.) There are other tools, like XIRR. But their ease of use (or not) depends on how your worksheet is set up. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating Annual Return From One Time Payment
Use the Rate function, as in:
=Rate((DateB-DateA)/365,0,-X,Y+X) Regards, Fred. "W" wrote in message ... Let's say you have an investment that costs $X on Date A and on Date B it pays $Y and you get back your principal. How do you calculate the annual rate of return for this simple investment in Excel? Assume that I already calculated the one time payment as a present value, based on the value at the start date of the investment. -- W |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating Annual Return From One Time Payment
I was only able to get the simple annual interest rate by adjusting your
formula to: =( ((X+Y)/ -X ) - 1 ) / ((B-A)/365) -- W "joeu2004" wrote in message ... On Jan 24, 2:49 pm, "W" wrote: Let's say you have an investment that costs $X on Date A and on Date B it pays $Y and you get back your principal. How do you calculate the annual rate of return for this simple investment in Excel? There are several ways to state the rate of return. The simple annual return is: =(X+Y)/X / ((B-A)/365) Note: For investments in traded securities, it is not uncommon to use 252 instead of 365 -- 252 being the typical number trading days per year. But that also requires that you determine the number of trading days between A and B, which is non-trivial. The compound annual rate of return (aka CAGR, compound annual growth rate) is any of the following equivalent formulas: =(X+Y)/X ^ (365/(B-A)) =rate(365/(B-A), 0, -X, X+Y) =(1 + rate(B-A, 0, -X, X+Y))^365 - 1 (But sometimes RATE requires a "guess" argument in order to return a valid result.) There are other tools, like XIRR. But their ease of use (or not) depends on how your worksheet is set up. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating Annual Return From One Time Payment
On Jan 24, 6:16 pm, "W" wrote:
I was only able to get the simple annual interest rate by adjusting your formula to: =( ((X+Y)/ -X ) - 1 ) / ((B-A)/365) First, sorry, there were a number of mistakes and unnecessary complexities in my formulas. I will provide corrected formulas below. But your -X concerns me. That suggests to me that your initial investment is recorded as a negative number. Is that right? Nothing wrong with that. But my formulas assumed that all values are recorded as positive numbers. If the initial investment is a negative number and the net return is a positive number, the correct formulas a Simple Return: =Y/(-X) / ((B-A)/365) Compound Return (aka CAGR): =((-X+Y)/(-X)) ^ (365/(B-A)) - 1 =rate(365/(B-A), 0, X, -X+Y) =(1 + rate(B-A, 0, X, -X+Y))^365 - 1 If the initial investment and net return are both positive numbers: Simple Return: =Y/X / ((B-A)/365) Compound Return (aka CAGR): =((X+Y)/X) ^ (365/(B-A)) - 1 =rate(365/(B-A), 0, -X, X+Y) =(1 + rate(B-A, 0, -X, X+Y))^365 - 1 HTH. ----- original posting ----- On Jan 24, 6:16*pm, "W" wrote: I was only able to get the simple annual interest rate by adjusting your formula to: * * =( ((X+Y)/ -X ) - 1 ) / ((B-A)/365) -- W "joeu2004" wrote in message ... On Jan 24, 2:49 pm, "W" wrote: Let's say you have an investment that costs $X on Date A and on Date B it pays $Y and you get back your principal. How do you calculate the annual rate of return for this simple investment in Excel? There are several ways to state the rate of return. The simple annual return is: =(X+Y)/X / ((B-A)/365) Note: *For investments in traded securities, it is not uncommon to use 252 instead of 365 -- 252 being the typical number trading days per year. *But that also requires that you determine the number of trading days between A and B, which is non-trivial. The compound annual rate of return (aka CAGR, compound annual growth rate) is any of the following equivalent formulas: =(X+Y)/X ^ (365/(B-A)) =rate(365/(B-A), 0, -X, X+Y) =(1 + rate(B-A, 0, -X, X+Y))^365 - 1 (But sometimes RATE requires a "guess" argument in order to return a valid result.) There are other tools, like XIRR. *But their ease of use (or not) depends on how your worksheet is set up. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculating Annual Growth Rate of Investment | Excel Discussion (Misc queries) | |||
Calculating return for multiple separate time periods | Excel Worksheet Functions | |||
Calculating the payment for a loan | Excel Worksheet Functions | |||
Annual Benefit Payment Tracker | Excel Discussion (Misc queries) | |||
average annual return | Excel Worksheet Functions |