calculating interest

[Kev]

I think this is straightforward, but I can't figure it out:

X principal is charged Y% daily compound interest at date Z.
What is the interest + principal at date A?

Beyond that, the amount of the principal is added to over time (the customer
adds to principal at Z date+something), and I need the same result, but I
assume I can do that as a separate calculation.

For example, Joe owes $100 on January 1 at 10%, compounded daily. What does
he owe, principal and interest, on December 31 of that year?

He owes an additional $50 on July 1 at 10%; what does he owe, principal and
interest, on December 31 of the year?

How can I set this up with each loanl as a record/row, with its start date
and end date and interest, and aggregate all like this:

principal/start_date/end_date/interest/principal/interest//interest+principa
l

Can I do this with absolute dates?

Thanx.

 

[Roger_H]

Hello

Hope this helps!!!

1. Let cell A1 = Start Date
2. Let cell B1 = Interest rate
3. Let cell C1 = Total Loan Amount
4. Let cell D1 = Initial Estimate of (or known) Loan Repayment amount

5. Now let cell A3 = Cell A1 and cell A4 = A3 +1; then drag (or
copy/paste) cells down column A as far in future as you need to go in
the future.

6. Let cell B3 =$C$1*((1+$B$1)^(1/365)-1)
7. Let cell C3 =$D$1-B3
8. Let cell D3 =$C$1-C3

9. Now let cell B4 =D3*((1+$B$1)^(1/365)-1)
10. Let cell C4 =$D$1-B4
11. Let cell D4 =D3-C4
12. Then copy the cells in range(b3:d3) and paste as far into the
future as you need to go.

13. Finally if you need to determine the loan repayment amount (which
you may have initially guessed in point 4. above). Go to Tools, Goal
Seek, then Set the cell at the bottom of column D to value 0 by
changing cell D1.


This algorithm will explicity show interest(column B), capital(column
C) and outstanding loan (column D) separately. If you want to make
additional borrowings and examine their impact upon interest and
capital repayment.....simply click on the cell in column D at the
appropriate date and press F2 and insert '+ loan amount'

Cheers

Roger