×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

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

Students Click Here

Jobs

Excel or Access calculating payments based on dates

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.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

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:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close