Hello Everyone,

I am in dire need of a solution ASAP to how I can get some figures on a loan amount.

Long story short I need to be able to create my own monthly statements for a loan that I gave a family member. I have called accountants to no avail. :(

I need to be able to enter in each months payment (or non payment), the account fee, the interest rate for that month & have it calculate how much interest is due & update the final balance on a monthly basis based off the entered figures.

The interest is compounded daily & is variable. I already have the numbers in for every day over the years (09-2011) where payments were made. But I really need to know where the loan was at in the beginning of 2012 (when i was told it had been paid off- I now suspect it wasn't).

I can't refer to the original account as I was making payments into & taking money out of the account.Hence the reason for needing this solution.

Both me & the family member need clear facts on what was paid & what is owing.

If someone has a sheet that I can enter the data into I would be ever so thankful- this has really stressed me out!

"AussieInNeed" wrote:
I need to be able to create my own monthly statements for a
loan that I gave a family member.
[....]
I need to be able to enter in each months payment (or non payment),
the account fee, the interest rate for that month & have it
calculate how much interest is due & update the final balance
on a monthly basis based off the entered figures.
The interest is compounded daily & is variable.

Usually, loan interest does not __actually__ compound, at least not daily.

That is a common misunderstanding because in some countries, the annual rate
is __stated__ as a compounded rate.

But that is just for "consistent" reporting purposes. It does not imply how
interest is actually computed periodically.

If loan interest does compound (due to insufficient payment), usually it is
at the same frequency as payments -- monthly, in this case. But even then,
often a penalty assessed instead of compounding interest. Interest just
accumulates at a "simple rate".

However, since this is a private loan, anything can happen.


"AussieInNeed" wrote:
I already have the numbers in for every day over the years
(09-2011) where payments were made. But I really need to know
where the loan was at in the beginning of 2012 (when i was told
it had been paid off- I now suspect it wasn't).

I'm a little confused by these facts.

On the one hand, you seem to say that you "have the numbers" through the end
of(?) 2011. That should tell you directly "where the loan was" at the
beginning of 2012; namely, the same was where it was at the end of 2011.

On the other hand, you qualify that by saying "where payments were made".
Are you implying that there were some missed payments during 2009 and 2011
(inclusive), and you need to compute adjusted numbers that will differ for
the numbers that you "have ... for ... over the years"?

It would be a lot easier if you shared some concrete numbers.

Anyway, take a look at the file "loan sched with var int rate.xls" at
https://www.box.com/s/yph6ng93jswkgxncje3c.

If it does not meet your needs, it might be a good start.