Which function to use?

[forevergrateful]

I need help!

I loaned someone money to someone a variety of times between '82 and '85.
The interest rate was fixed at the time. From then till now some of the
money has been repaid, but only a few times over the full period.

Which function would help me know the present value of the loan including
interest and the amounts already returned, as well as the balance at the end
of each year?

Any help would be greatly appreciated.

FG

 

[Harlan Grove]

I loaned someone money to someone a variety of times between '82 and '85.
The interest rate was fixed at the time. From then till now some of the
money has been repaid, but only a few times over the full period.

Which function would help me know the present value of the loan including
interest and the amounts already returned, as well as the balance at the end
of each year?

Noncommercial, unsecured loans with an average loan date more than 2 decades
ago with sporadic subsequent repayments? The glib answer for the present
value of the loans would be zero, at least as a stream of future payments.

If you think there's some chance the loans will still be paid back, then the
way you calculate loan principal, interest and total balances is to set up a
table from time 0 (when a loan was made) in the top row and paymens in
subsequent rows. Dates in 1st column, payments in 2nd column, principal
portion of payments in 3rd column, interest portion of payments in 4th
column, and total loan balance in 5th column. Dates, initial balance (loan
amount in 5th column of first row), and subsequent payments are entries. All
other cells are formulas. There are no formulas in the 2nd through 4th
columns in the first row. For simplicity, I'll start this in cell A1. My
approach is equivalent to allowing negative amortization.

A1: entry - date loan made
E1: entry - loan amount
A2: entry - date of first payment
B2: entry - amount of first payment
C2: =MAX(0,B2-D2)
D2: =E1*(((1+i/12)^(1/30.4375))^(A2-A1)-1)
E2: =E1-B2+D2

As you add entries in columns A and B, select the cells in the preceding
row's columns C through E and fill down into the row for the latest payment.
For example, after you enter the second payment's date and amount in A3 and
B3, respectively, select C2:E2 and fill down one row into C3:E3.

The term i in the D2 formula above is the interest rate assumed to be an
APR.

 

[forevergrateful]

Unfortunately, I'm still confused as to how to pull this off. Here are some
of the details to help illustrate what I'm trying to accomplish:

Date Loan
6/5/1986 2,400.00
6/9/1986 600.00
6/9/1986 300.00
9/1/1986 50
9/1/1986 1,200.00
9/1/1986 450
1/13/1987 4,561.27

Date Payment
9/24/1986 -1,000.00
12/29/1986 -2,700.00
3/3/1987 -500
8/1/1998 -100
6/22/1999 -1,500.00
7/11/2003 -2,000.00


So, as you can see, there are a variety of loan dates and a variety of
payment dates. What I'd like to do is add interest to the principal amount
at the end of each year and know the principal at the end of each year. Is
there a way to do this based on a fixed interest rate from the beginning?

I'm sorry to be a moron, but I'm really struggling with finding a way to do
this that is accurate and relatively simple to update as new payments /
loans and interest are included.

Thanks for any help / explanation that can be offered.

FG