Simple Interest with irregular expenditures, irregular reimbursements at irregular intervals

[Frank B Denman]

Hi Folks,

I'm trying to help a customer who has been making reimbursable expenditures for
a family estate at irregular intervals over the last 18 years. During this
period, the estate has occasionaly sent partial reimbursements. A time of
reckoning now approaches. The understanding is that the estate will pay simple
interest on the expenses. No interest on the interest.

I can visualize a spreadsheet with a row for every month across the 18 years,
and with all expenditures and payments treated as if they happened on the first
of the month.

But this seems like kind of a kludge.

Is there an elegant way to do this?

Thanks.

Frank




Frank Denman
Denman Systems
(e-mail address removed)
Please delete the "x" from my email address.

 

[Harlan Grove]

I'm trying to help a customer who has been making reimbursable expenditures
for a family estate at irregular intervals over the last 18 years. During
this period, the estate has occasionaly sent partial reimbursements. A time
of reckoning now approaches. The understanding is that the estate will pay
simple interest on the expenses. No interest on the interest.

I can visualize a spreadsheet with a row for every month across the 18
years, and with all expenditures and payments treated as if they happened
on the first of the month.

But this seems like kind of a kludge.

Is there an elegant way to do this?

I have to question the interest in elegance if you already have a working
solution. Kludgy or not, what you propose would work.

However, I like a pointless academic challenge as much as the next
respondent in this ng, and there is a more compact way to do this. Enter
*all* expenditures and reimbursements in a table with date (month and year)
in the first column and amount (+ for expenditures, - for reimbursements) in
the second column. Name this table TBL. Enter the reckoning date in another
cell named RD. Enter your annual simple interest rate in yet another cell
named ASIR. Then use the formula

=ROUND(SUMPRODUCT(1+ASIR*DATEDIF(INDEX(TBL,0,1),RD,"M")/12,
INDEX(TBL,0,2)),2)

 

[Frank B Denman]

I have to question the interest in elegance if you already have a working
solution.
Kludgy or not, what you propose would work.

However, I like a pointless academic challenge as much as the next
respondent in this ng, and there is a more compact way to do this. Enter
*all* expenditures and reimbursements in a table with date (month and year)
in the first column and amount (+ for expenditures, - for reimbursements) in
the second column. Name this table TBL. Enter the reckoning date in another
cell named RD. Enter your annual simple interest rate in yet another cell
named ASIR. Then use the formula

=ROUND(SUMPRODUCT(1+ASIR*DATEDIF(INDEX(TBL,0,1),RD,"M")/12,
INDEX(TBL,0,2)),2)

You've reassured me, solved my problem, & made me laugh too.

Thanks!

Frank
Frank Denman
Denman Systems
(e-mail address removed)
Please delete the "x" from my email address.