×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

# Excel or Access calculating payments based on dates

## Excel or Access calculating payments based on dates

(OP)
Hello there I have a nice task.

I need to calculate a total per year payments for a 5 year period given a start and ending date and a monthly payment.

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)

Is there a way to write a formula that will calculate the total per year monthly payments based on start and end date?

example:
if start date = 06/01/2000 and
end date = 06/01/2001 and
Monthly payment = $100.00 I need to get: 2000 total monthly payments =$600 and
2001 total monthly payments = $600 Any help is greatly appreciated. I will also post this on the access forum Thanks again.... Dumboy.! Ouch.! .my brain..... spiderdesign@yahoo.com It's hard to think with only one neuron......Ouch.....!....it hurts when I think......! ### RE: Excel or Access calculating payments based on dates (OP) This works for the first part (start date) I used the formula: =(MONTH("12/31/1998"-E4))*G4 Where Month corresponds to the column for 1998 and E4 corresponds to the start date and G4 is the monthly payment. now... the second part what should I include in the formula to tell access when to stop Note that my current formula would not work if the end date where september 1998. The 12/31/1998 has to be replaced... but with what....? Eliiiiiizaaaaaabeeeeethhhhhhh...........! DooooooooooouuuuuuuuughP...........! Dumboy.! Ouch.! .my brain..... spiderdesign@yahoo.com It's hard to think with only one neuron......Ouch.....!....it hurts when I think......! ### RE: Excel or Access calculating payments based on dates There is a Wizard that comes with Excel It's on the Office Installation CD and has to be gotten explicitly from there. Its called Loan.xls Look on the CD in this folder location \Valupack\Templates\Excel\Loan.xls It does amortization which may help in your formula creation ### RE: Excel or Access calculating payments based on dates (OP) GOT IT..... Just in case somebody cares..... I DID IT.... yeahh.......! my mommy Elizabeth... and Daddy DougP would be proud of me..... assuming that:$E4 = Start date (of payments)
$F4 = End date (of payments)$H4 = Monthly payment amount
I$2 = The columns for the Total per year payments The following formula will detect on the yearly columns: No payment if the start date is less than the current column year Partial Payment if the start date and the column year are in the same year regular payments (full year) Payment for partial year if the End Date and the column year are the same No payment if the Column year is greater than the end date =IF(I$2<=$E4,"0",IF(YEARFRAC(I$2,$E4,0)<=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)>YEAR($F4),"NADA",\$H4*12))))

Pretty good for a mono-neuron kind of person......... uh!

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....?

Got to go...... left...right...left...right...left...left....Ouch.....!....right...left....

Dumboy.! Ouch.! .my brain.....
spiderdesign@yahoo.com

It's hard to think with only one neuron......Ouch.....!....it hurts when I think......!

### RE: Excel or Access calculating payments based on dates

(OP)
Sorry about the wrap....... not my fault... do!

Dumboy.! Ouch.! .my brain.....
spiderdesign@yahoo.com

It's hard to think with only one neuron......Ouch.....!....it hurts when I think......!

#### Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

#### Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Close Box

# Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!