OFFSET /EOMONTH - Monthly returns

[summerguy]

Can someone please help?

I am trying to write the formula to calculate monthly returns. I am using different variations of OFFSET/ EOMONTH to do this but have been unable to figure this out. The formula I looking for is for cell F2 - (value on last day of month - value on first day of month or start date) / value on first day of month or start date. For July this would be (value on 7/1/2010 - value on 7/30/2010)/value on 7/30/2010.

</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:131px;" /><col style="width:131px;" /><col style="width:131px;" /><col style="width:131px;" /><col style="width:131px;" /><col style="width:131px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#333333; color:#ffffff; font-weight:bold; text-align:center; ">Date </td><td style="background-color:#333333; color:#ffffff; font-weight:bold; text-align:center; ">Weekday</td><td style="background-color:#333333; color:#ffffff; font-weight:bold; text-align:center; ">Value</td><td style="background-color:#333333; color:#ffffff; font-weight:bold; font-family:Arial; font-size:10pt; text-align:center; ">Daily Return</td><td style="background-color:#333333; color:#ffffff; font-weight:bold; font-family:Arial; font-size:10pt; text-align:center; ">Weekly Return</td><td style="background-color:#333333; color:#ffffff; font-weight:bold; text-align:center; ">Monthly Return</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">6/16/2010</td><td >Wednesday</td><td style="background-color:#ffff00; text-align:right; ">1,000.00</td><td style="text-align:right; ">0.00%</td><td style="text-align:right; ">10.00%</td><td style="text-align:right; ">50.00%</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">6/17/2010</td><td >Thursday</td><td style="text-align:right; ">1,050.00</td><td style="text-align:right; ">5.00%</td><td style="text-align:right; ">22.73%</td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">6/18/2010</td><td >Friday</td><td style="text-align:right; ">1,100.00</td><td style="text-align:right; ">4.76%</td><td style="text-align:right; ">18.52%</td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">6/21/2010</td><td >Monday</td><td style="text-align:right; ">1,150.00</td><td style="text-align:right; ">4.55%</td><td style="text-align:right; ">15.63%</td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; ">6/22/2010</td><td >Tuesday</td><td style="text-align:right; ">1,200.00</td><td style="text-align:right; ">4.35%</td><td style="text-align:right; ">13.51%</td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:right; ">6/23/2010</td><td >Wednesday</td><td style="text-align:right; ">1,250.00</td><td style="text-align:right; ">4.17%</td><td style="text-align:right; ">11.90%</td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="text-align:right; ">6/24/2010</td><td >Thursday</td><td style="text-align:right; ">1,300.00</td><td style="text-align:right; ">4.00%</td><td style="text-align:right; ">10.64%</td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="text-align:right; ">6/25/2010</td><td >Friday</td><td style="text-align:right; ">1,350.00</td><td style="text-align:right; ">3.85%</td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="text-align:right; ">6/28/2010</td><td >Monday</td><td style="text-align:right; ">1,400.00</td><td style="text-align:right; ">3.70%</td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="text-align:right; ">6/29/2010</td><td >Tuesday</td><td style="text-align:right; ">1,450.00</td><td style="text-align:right; ">3.57%</td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="text-align:right; ">6/30/2010</td><td >Wednesday</td><td style="background-color:#ffff00; text-align:right; ">1,500.00</td><td style="text-align:right; ">3.45%</td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="text-align:right; ">7/1/2010</td><td >Thursday</td><td style="text-align:right; ">1,550.00</td><td style="text-align:right; ">3.33%</td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td style="text-align:right; ">7/2/2010</td><td >Friday</td><td style="text-align:right; ">1,600.00</td><td style="text-align:right; ">3.23%</td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td style="text-align:right; ">7/5/2010</td><td >Monday</td><td style="text-align:right; ">1,650.00</td><td style="text-align:right; ">3.13%</td><td > </td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000;

><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >D2</td><td >=(C2-C2)/C2</td></tr><tr><td >E2</td><td >=(C4-C2)/C2</td></tr><tr><td >F2</td><td >=(C12-C2)/C2</td></tr><tr><td >D3</td><td >=(C3-C2)/C2</td></tr><tr><td >E3</td><td >=(<span style=' color:008000; '>(<span style=' color:#0000ff; '>(OFFSET<span style=' color:#ff0000; '>($C$4,5*<span style=' color:#804000; '>(ROWS<span style=' color:#ff7837; '>($A$2:A2)</span>)</span>,0,1)</span>)</span>-OFFSET<span style=' color:#0000ff; '>($C$4,5*<span style=' color:#ff0000; '>(ROWS<span style=' color:#804000; '>($A$2:A2)</span>-1)</span>,0,1)</span>)</span>/<span style=' color:008000; '>(OFFSET<span style=' color:#0000ff; '>($C$4,5*<span style=' color:#ff0000; '>(ROWS<span style=' color:#804000; '>($A$2:A2)</span>-1)</span>,0,1)</span>)</span>)</td></tr></table></td></tr></table> <br /><br /><span style="font-family:Arial; font-size:9pt; font-weight:bold;background-color:#ffffff; color:#000000;

 

[summerguy]

CORRECTION: for July the formula is (value on 7/30/2010-value on 6/30/2010)/value on 6/30/2010

 

[njimack]

Something like this?

=(INDEX(C2:C100,MATCH(DATE(YEAR(TODAY()),MONTH(TODAY())+1,0),A2:A100,0))/INDEX(C2:C100,MATCH(DATE(YEAR(TODAY()),MONTH(TODAY()),0),A2:A100,0)))-1

 

[Andrew Poulsom]

For June:

=(INDEX(C2:C15,MATCH(MAX(IF(MONTH(A2:A15)=MONTH(A2),A2:A15)),A2:A15,FALSE))-INDEX(C2:C15,MATCH(MIN(IF(MONTH(A2:A15)=MONTH(A2),A2:A15)),A2:A15,FALSE)))/INDEX(C2:C15,MATCH(MIN(IF(MONTH(A2:A15)=MONTH(A2),A2:A15)),A2:A15,FALSE))

confirmed with Ctrl+Shift+Enter.

 

[summerguy]

Hi Neil - thank you for the code. It does not appear to work and returns: #N/A

 

[summerguy]

Thank you Andrew. How can I adapt this code so I can drag this down and generate monthly returns from the daily data for multiple months?

 

[Andrew Poulsom]

You can make the row references absolute:

=(INDEX(C$2:C$15,MATCH(MAX(IF(MONTH(A$2:A$15)=MONTH(A2),A$2:A$15)),A$2:A$15,FALSE))-INDEX(C$2:C$15,MATCH(MIN(IF(MONTH(A$2:A$15)=MONTH(A2),A$2:A$15)),A$2:A$15,FALSE)))/INDEX(C$2:C$15,MATCH(MIN(IF(MONTH(A$2:A$15)=MONTH(A2),A$2:A$15)),A$2:A$15,FALSE))

Don't forget Ctrl+Shift+Enter.

 

[summerguy]

Hi Andrew,

The issue that arises when I make the row references absolute and drag down is that the following months formula is (value on last day of month - value on first day of month) / value on first day of month

The correct formula is: (value on last day of month - value on last day of previous month) / value on last day of previous month.

 

[Andrew Poulsom]

You don't have a value on last day of previous month for June. For July in F13:

=(INDEX(C$2:C$15,MATCH(MAX(IF(MONTH(A$2:A$15)=MONTH(A13),A$2:A$15)),A$2:A$15,FALSE))-INDEX(C$2:C$15,MATCH(MAX(IF(MONTH(A$2:A$15)=MONTH(DATE(YEAR(A13),MONTH(A13)-1,1)),A$2:A$15)),A$2:A$15,FALSE)))/INDEX(C$2:C$15,MATCH(MAX(IF(MONTH(A$2:A$15)=MONTH(DATE(YEAR(A13),MONTH(A13)-1,1)),A$2:A$15)),A$2:A$15,FALSE))

 

[summerguy]

Thank you Andrew. This works now.