RECEIVED function/Daily compound interest

Question

Barb2008 Author

Level 1

0 points

RECEIVED function/Daily compound interest

I am trying to figure out how large a debt is at several points in time and can't seem to find the right formula to do that. The payments being made are variable and are occurring at variable periods of time.

The RECEIVED function seems to be the closest to what I want, but perhaps not quite right. The interest rate is 5% annually, compounded daily. The RECEIVED function contains an annual interest rate as one of its arguments, but it doesn't seem to say anywhere what the compounding period is. From that I assume it must be annual. What happens when the period of time I put in for the first and second dates is less than a year?

Is there some other formula I should be using? How can I make sure the formula is compounding daily?

By the way, I was not able to get the RECEIVED function to accept manually typed-in dates, no matter what format I put them in. It was only when I referred to the date in another cell that it worked. I note that others have had problems with the dates when using this function, so thought I would mention that.

Thanks.

MacBook, Mac OS X (10.6.8)

Posted on Oct 24, 2011 11:40 AM

Reply

Answer 1

Oct 24, 2011 11:46 AM in response to Barb2008

Barb,

The period is whatever you want it to be. The function doesn't care. If you say that the interest rate is 5% per period, that period could be a day, a month or a year. Just pick a period and stick with it. Compunding occurs on a per period basis. If you wish to compound daily, then your period had better be a day, and in your case, the interest rate would be 5%/365.

Jerry

Reply

Answer 2

Oct 24, 2011 12:16 PM in response to Barb2008

Continuing...

I was giving some general advice on functions above, but not on the RECEIVED function specifically. I don't believe that the RECEIVED function is the one you want.

I'm wondering how your records are arranged. I assume a payment leger with Date and Amount Paid entries and a balance due result. If that's the case, we can work up a set of formulas for your case.

Jerry

Reply

Answer 3

Badunit

Level 7

31,341 points

Oct 24, 2011 12:19 PM in response to Barb2008

To use a date in the formula, you can create it with the DATE function or you can enclose the date in quotes like "5/1/2009". The DATE function is more robust in that it is not affected by your localization whereas "5/1/2009" could be May 1, 2009 or Jan 5, 2009.

Reply

Answer 4

Barb2008 Author

Level 1

0 points

Oct 24, 2011 12:21 PM in response to Jerrold Green1

Hi Jerry,

Thanks for your prompt reply. When you say "pick a period", are you referring to the 5th argument in the function, called "days-basis"? This is what the Guide says about it, on p. 138:

days-basis: An optional argument specifying the number of days per month and days per year used in the calculations.

30/360 (0 or omitted): 30 days in a month, 360 days in a year, using the NASD method for dates falling on the 31st of a month.

actual/actual (1): Actual days in each month, actual days in each year.

actual/360 (2): Actual days in each month, 360 days in a year.

actual/365 (3): Actual days in each month, 365 days in a year.

30E/360 (4): 30 days in a month, 360 days in a year, using the European method for dates falling on the 31st of a month (European 30/360).

I don't see how any of this refers to a compounding period. If it does, which one would you select for daily?

Barb

Reply

Answer 5

Barb2008 Author

Level 1

0 points

Oct 24, 2011 12:25 PM in response to Badunit

Badunit,

So you say (about the date), but try it yourself. I could not get the date to work in the formula, with or without quotes, forwards, backwards, or sideways. As well, it told me that the date in argument 1 had to be smaller than the date in argument 2, and it was smaller, but no matter, the poor thing was still confused about what I meant (until I put the dates in other cells, as I mentioned.) It must like you more than it does me.

😉

Reply

Answer 6

Barb2008 Author

Level 1

0 points

Oct 24, 2011 12:27 PM in response to Jerrold Green1

Yes, it is set up that way. I have dates, payments, running total, etc.

Reply

Answer 7

Badunit

Level 7

31,341 points

Oct 24, 2011 12:43 PM in response to Barb2008

I tried it before posting. It worked fine for me. I tried it with dates in separate cells, with one date typed into the formula (using quotes) and the other in a separate cell, with both typed into the formula, and I tried it using the DATE function. I got the same answer each time and no errors.

Reply

Answer 8

Oct 24, 2011 12:54 PM in response to Barb2008

Barb2008 wrote:

Yes, it is set up that way. I have dates, payments, running total, etc.

We have 2 header rows. Formulas are as follows.

Column C body rows: =FV(5%/365, DUR2DAYS(OFFSET($A$1, ROW()-1, 0)-OFFSET($A$1, ROW()-2, 0)), 0, -OFFSET($C$1, ROW()-2, 0))-B

It could be more concise, but I used the offset function in case you ever decide to sort or delete/add rows, so the expression wouldn't fail. The example has an initial amount of $1000. Note that I have skipped some payments, so on one occasion the balance rises because the payment doesn't cover the interest.

Jerry

Reply