$20,000 due. Pays diff $ every x. Int calc per annum. Need excel .

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

A judgment was obtained for $20,000 in '98. He has paid different amounts
ever since. I need formula in excel to figure what his balance is taking
into account the principle and interest deductions that have been made b/c of
payments. He has an interest rate that is compounded daily on a per annum
basis.
 
Take a look at the XNPV function which calculates the Net Present Value of a
stream of irregular cash flows.

I think that'll do what you're looking for.

Regards,
Ron
 
You need to to find the dates of each payment and amounts paid Then take the
Future value of each cash flow and add the total to get to what is owed today.
for the 20,000 loan your formula will look like this say rate is 10% per year
=-20000*((1+0.1/365)^(TODAY()-(DATE(1998,1,1))))

Then for you cash payments that you made during the years say 2500 on
1/1/2001 use the following:-
=+2500*((1+0.1/365)^(TODAY()-(DATE(2001,1,1))))
(notice that the sign is positive for loan repayments)

For each of the cash flows use this formula adjusting the dates and the
amount repaid.
Then add all of these cash flows and that should be your value that you owe
today.
 
Back
Top