Excel or Access calculating payments based on dates

[Dumboy]

Hello there I have a nice task.<br><br>I need to calculate a total per year payments for a 5 year period given a start and ending date and a monthly payment.<br><br>Keep in mind that some payments started on 1999 and some will in 2000, some in 2001..etc.. (note: the start date will always be the 1st of the month and the end date the last of the month)<br><br>Is there a way to write a formula that will calculate the total per year monthly payments based on start and end date?<br><br>example:<br>if start date = 06/01/2000 and<br>&nbsp;&nbsp;&nbsp;end date = 06/01/2001 and<br>Monthly payment = $100.00<br><br>I need to get:<br><br>2000 total monthly payments = $600 and<br>2001 total monthly payments = $600<br><br>Any help is greatly appreciated.<br><br>I will also post this on the access forum<br><br>Thanks again....<br> <p>Dumboy.! Ouch.! .my brain.....<br><a href=mailto:spiderdesign@yahoo.com>spiderdesign@yahoo.com</a><br><a href= > </a><br>It's hard to think with only one neuron......Ouch.....!....it hurts when I think......!

 

[Dumboy]

<br><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;This works for the first part (start date)<br><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;I used the formula:<br><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=(MONTH(&quot;12/31/1998&quot;-E4))*G4<br><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Where Month corresponds to the column for 1998 and E4 corresponds to the<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;start date and G4 is the monthly payment.<br><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;now... the second part what should I include in the formula to tell access when<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;to stop<br><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Note that my current formula would not work if the end date where september<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1998.<br><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;The 12/31/1998 has to be replaced... but with what....?<br><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Eliiiiiizaaaaaabeeeeethhhhhhh...........!<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;DooooooooooouuuuuuuuughP...........! <p>Dumboy.! Ouch.! .my brain.....<br><a href=mailto:spiderdesign@yahoo.com>spiderdesign@yahoo.com</a><br><a href= > </a><br>It's hard to think with only one neuron......Ouch.....!....it hurts when I think......!

 

[DougP]

There is a Wizard that comes with Excel<br>It's on the Office Installation CD and has to be gotten explicitly from there.<br><br>Its called Loan.xls<br>Look on the CD in this folder location<br>&nbsp;\Valupack\Templates\Excel\Loan.xls<br><br>It does amortization which may help in your formula creation<br><br>

 

[Dumboy]

GOT IT..... Just in case somebody cares.....<br><br>I DID IT.... yeahh.......! my mommy Elizabeth... and Daddy DougP would be proud of me.....<br><br>assuming that:<br>$E4 = Start date (of payments)<br>$F4 = End date (of payments)<br>$H4 = Monthly payment amount<br>I$2 = The columns for the Total per year payments<br><br>The following formula will detect on the yearly columns:<br>No payment if the start date is less than the current column year<br>Partial Payment if the start date and the column year are in the same year<br>regular payments (full year)<br>Payment for partial year if the End Date and the column year are the same<br>No payment if the Column year is greater than the end date<br><br>=IF(I$2&lt;=$E4,&quot;0&quot;,IF(YEARFRAC(I$2,$E4,0)&lt;=1,(YEARFRAC(I$2,$E4,0)*12)*$H4,IF(YEAR(I$2)=YEAR($F5),($H4*12)-((MONTH(I$2)-MONTH($F4))*$H4),IF(YEAR(I$2)&gt;YEAR($F4),&quot;NADA&quot;,$H4*12))))<br><br>Pretty good for a mono-neuron kind of person......... uh!<br><br>I just posted this because somebody maybe in a similar need and we are here to help each other not just to serve our own selfish needs........ who said that....?<br><br>Got to go...... left...right...left...right...left...left....Ouch.....!....right...left.... <p>Dumboy.! Ouch.! .my brain.....<br><a href=mailto:spiderdesign@yahoo.com>spiderdesign@yahoo.com</a><br><a href= > </a><br>It's hard to think with only one neuron......Ouch.....!....it hurts when I think......!

 

[Dumboy]

Sorry about the wrap....... not my fault... do! <p>Dumboy.! Ouch.! .my brain.....<br><a href=mailto:spiderdesign@yahoo.com>spiderdesign@yahoo.com</a><br><a href= > </a><br>It's hard to think with only one neuron......Ouch.....!....it hurts when I think......!