VARYING COMPOUND INTEREST

[RUFUS911]

What's the best way to create the following table:

I have 100 tenants residing in 15 different properties. I need to return to them their security deposits, with daily compounded interest, to them when they move out. The interest rate will change every month. None of the security deposits are the same amount. People move in and out all the time. Forfeitures must be taken into consideration. Using Excel 2000 Pro.

 

[Lady Layla]

Follow instructions as set forth in your text book


: What's the best way to create the following table:
:
: I have 100 tenants residing in 15 different properties. I need to return to
them their security deposits, with daily compounded interest, to them when they
move out. The interest rate will change every month. None of the security
deposits are the same amount. People move in and out all the time. Forfeitures
must be taken into consideration. Using Excel 2000 Pro.

 

[Dave R.]

Hehehe.

 

[Frank Kabel]

Hi
so you assume this is homework <vbg>

 

[RUFUS911]

No, I don't. I don't know excel and my boss doesn't either. Either everyone get serious and try to help (Isn't that what this forum is for?), or keep your snide comments to yourself.

 

[Dave R.]

Simmer down Rufus! no need to get riled up here..

Why don't you lay the table out in the most logical way for your operation,
then come back here after you have a basic layout for help with the
formulas. Very few people are going to create an entire spreadsheet for you
especially since your question is so generic that it leaves so many
different (and wrong) ways to do things for your specific needs.

Your question seems like homework for a few reasons;
1) uses very generic numbers
2) has no hint as to the layout of the data, as if this is something that is
being done from scratch (say, from a homework assignment)
3) related to #2, for a business with 15 properties and 100+ tenants, why is
someone with no clue whatsoever being given the task of calculating such a
thing?

Good luck.

No, I don't. I don't know excel and my boss doesn't either. Either

everyone get serious and try to help (Isn't that what this forum is for?),
or keep your snide comments to yourself.

 

[Norman Harker]

Hi Rufus!

To calculate daily compound interest:

I'll assume that a Nominal rate compounded daily has been quoted in
the agreement:

=Deposit*(1+NomRate/365)^(DepositDate-StartDate)

If some other rate has been quoted, you'll need to convert to daily
effective equivalent first.

Example:
1000 security deposit taken 1-Jun-2002
Tenant departs 11-Jun-2004
Rate of interest 6% per annum nominal compounded daily

=1000*(1+6%/365)^("11-Jun-2004"-"1-Jun-2002")
Returns: 1129.52615016454

Rather than use hard coded date calculations you are better off using
Cell references.

But the main issue is that you must use the effective rate per day and
compound for the day count.

 

[Harlan Grove]

What's the best way to create the following table:

I have 100 tenants residing in 15 different properties. I need to return to
them their security deposits, with daily compounded interest, to them when
they move out. The interest rate will change every month. None of the security
deposits are the same amount. People move in and out all the time. Forfeitures
must be taken into consideration. Using Excel 2000 Pro.

Number of different properties is irrelevant.

If the interest rate changes every month, easiest to use a table with three
columns, the first column holding date values for the first of each month going
as far back as needed, the second holding the date values for the last day in
the month, and the third column holding the nominal annual interest rates
compounded daily. If this table were in A1:C24, then the value of the security
deposit at the date a tenant moves out would be given by the array formula

=PRODUCT(OriginalDepositAmount,(1+$C$1:$C$24/365)
^IF((B1:B24<MoveInDate)+(A1:A24>MoveOutDate),0,
IF(MoveOutDate>B1:B24,B1:B24+1,MoveOutDate+1)
-IF(MoveInDate<=A1:A24,A1:A24,MoveInDate+1)))