dianemarie
Instructor
Hello, I have this data in a worksheet:
Debtor Debt DateIssued YrsToPay DueOnAnniversary
John Doe $500 1/1/03 4 $125
John Doe $1000 1/1/02 5 $200
Mary Smith $500 5/3/03 5 $100
etc
(DueOnAnniversary is simply the debt divided by number of years to pay.)
What I would like to be able to do is enter a date and debtor name, and have Excel calculate that debtor's obligation as of the date entered. The first payment is not due until the first anniversary. So if I entered today's date, John Doe would be obligated for $200 only. (The first anniversary date has passed for the $1000 debt, so he would be obligated for $200, but because the $500 debt has not yet reached the anniversary, there is no payment due. However, if I put in a date of 1/1/04, then John Doe would owe $525 (two payments from the $1000 debt and one payment from the $500 debt). Can Excel handle this or would it be better handled in Access? Thank you for any help or suggestions.
Debtor Debt DateIssued YrsToPay DueOnAnniversary
John Doe $500 1/1/03 4 $125
John Doe $1000 1/1/02 5 $200
Mary Smith $500 5/3/03 5 $100
etc
(DueOnAnniversary is simply the debt divided by number of years to pay.)
What I would like to be able to do is enter a date and debtor name, and have Excel calculate that debtor's obligation as of the date entered. The first payment is not due until the first anniversary. So if I entered today's date, John Doe would be obligated for $200 only. (The first anniversary date has passed for the $1000 debt, so he would be obligated for $200, but because the $500 debt has not yet reached the anniversary, there is no payment due. However, if I put in a date of 1/1/04, then John Doe would owe $525 (two payments from the $1000 debt and one payment from the $500 debt). Can Excel handle this or would it be better handled in Access? Thank you for any help or suggestions.