I am trying to develop a spreadsheet that will enable me to calculate the exact amount of accrued interest (compounded annually based on actual investment date). Typically I would just use a FV formula, but that only works if there is an investment made DAY 1 and no subsequent transactions afterwards. I am struggling with scenarios in which more than one investment is made over a period of time (at various intervals) or in which a distribution has been made, thereby reducing the amount of accrued interest.
Below is the beginnings of a basic example.
Date Investment Distribution
11/29/07 $100,000 $0
1/28/08 $20,000 $0
10/12/08 $0 ($15,000)
I would like to be able to create either a simple table or a couple of formulas that could easily tell me the amount of accrued interest on any given day. In the past I've done this by setting up a simple ledger that shows ins and outs and interest accruing, but for long investments this can get quite long.
Any ideas?
Below is the beginnings of a basic example.
Date Investment Distribution
11/29/07 $100,000 $0
1/28/08 $20,000 $0
10/12/08 $0 ($15,000)
I would like to be able to create either a simple table or a couple of formulas that could easily tell me the amount of accrued interest on any given day. In the past I've done this by setting up a simple ledger that shows ins and outs and interest accruing, but for long investments this can get quite long.
Any ideas?
