I have a suggestion that we will be very useful for our company.
The way Excel calculate PERCENTILE and QUARTILE series is not appropiate for
discrete numbers.

My suggestion
At the moment =percentile(a1:a10,.05) gives 5.5 in the sequence
{1,2,3,4,5,6,7,8,9,10}. This is incorrect on discrete numbers.

Following the example above, I want to gather discrete numbers, i.e. "5" on
the series rather than "5.5". The same apply for QUARTILE formulas where a
second parameter is needed.
Add a Third parameter [optional] as follows:
- 0, default (as it currently works)
- 1, takes the inferior discrete number
- 2, takes the superior discrete number


----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc

=INT(PERCENTILE(A1:A10,0.5))

"Francisco" wrote:

I have a suggestion that we will be very useful for our company.
The way Excel calculate PERCENTILE and QUARTILE series is not appropiate for
discrete numbers.

My suggestion
At the moment =percentile(a1:a10,.05) gives 5.5 in the sequence
{1,2,3,4,5,6,7,8,9,10}. This is incorrect on discrete numbers.

Following the example above, I want to gather discrete numbers, i.e. "5" on
the series rather than "5.5". The same apply for QUARTILE formulas where a
second parameter is needed.
Add a Third parameter [optional] as follows:
- 0, default (as it currently works)
- 1, takes the inferior discrete number
- 2, takes the superior discrete number


----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc

I see your point but this method does not work with large series.
The PERCENTILE value must be a number that belongs to the series.

We do tables of salaries and we need to show the salary that best represents
the MEDIAN or a QUARTILE. For example:
{$50000, $50001 $50010 $50500 $50501 $65000}
The right MEDIAN value is $50010 and Excel provides $50255, this is just
wrong. $50255 does not belong the series.

"BoniM" wrote:

=INT(PERCENTILE(A1:A10,0.5))

"Francisco" wrote:

I have a suggestion that we will be very useful for our company.
The way Excel calculate PERCENTILE and QUARTILE series is not appropiate for
discrete numbers.

My suggestion
At the moment =percentile(a1:a10,.05) gives 5.5 in the sequence
{1,2,3,4,5,6,7,8,9,10}. This is incorrect on discrete numbers.

Following the example above, I want to gather discrete numbers, i.e. "5" on
the series rather than "5.5". The same apply for QUARTILE formulas where a
second parameter is needed.
Add a Third parameter [optional] as follows:
- 0, default (as it currently works)
- 1, takes the inferior discrete number
- 2, takes the superior discrete number


----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc

50255 is the median of the sequence of numbers you give. A set of six values
does not have a median exactly equal to the value of any of the numbers.
Rather, it is equal to the third largest value plus the fourth largest value
divided by two.

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"Francisco" wrote:

I see your point but this method does not work with large series.
The PERCENTILE value must be a number that belongs to the series.

We do tables of salaries and we need to show the salary that best represents
the MEDIAN or a QUARTILE. For example:
{$50000, $50001 $50010 $50500 $50501 $65000}
The right MEDIAN value is $50010 and Excel provides $50255, this is just
wrong. $50255 does not belong the series.

"BoniM" wrote:

=INT(PERCENTILE(A1:A10,0.5))

"Francisco" wrote:

I have a suggestion that we will be very useful for our company.
The way Excel calculate PERCENTILE and QUARTILE series is not appropiate for
discrete numbers.

My suggestion
At the moment =percentile(a1:a10,.05) gives 5.5 in the sequence
{1,2,3,4,5,6,7,8,9,10}. This is incorrect on discrete numbers.

Following the example above, I want to gather discrete numbers, i.e. "5" on
the series rather than "5.5". The same apply for QUARTILE formulas where a
second parameter is needed.
Add a Third parameter [optional] as follows:
- 0, default (as it currently works)
- 1, takes the inferior discrete number
- 2, takes the superior discrete number


----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc

Dave,
That is not entire true, there are 2 methods. That is fine for certain
cases, however when it is necessary to take a value that belongs to the
series this method is not appropriated. The key is: "it has to belong". Excel
should allow me to fine tune this.

I have another example where Excel has the WRONG calculation:
{10,20,30,40,50,60,70,80,90,100}
What do you expect if you do =quartile(RANGE,3), Upper Quartile?
Yes, you will expect 75 as the Upper Quartile .... but surprise surprise
....Excel shows 77.5.
This is just plain wrong.

What I am saying is to extend NOT to replace the funcionality to have a more
appropiate calculation.

F

"Dave F" wrote:

50255 is the median of the sequence of numbers you give. A set of six values
does not have a median exactly equal to the value of any of the numbers.
Rather, it is equal to the third largest value plus the fourth largest value
divided by two.

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"Francisco" wrote:

I see your point but this method does not work with large series.
The PERCENTILE value must be a number that belongs to the series.

We do tables of salaries and we need to show the salary that best represents
the MEDIAN or a QUARTILE. For example:
{$50000, $50001 $50010 $50500 $50501 $65000}
The right MEDIAN value is $50010 and Excel provides $50255, this is just
wrong. $50255 does not belong the series.

"BoniM" wrote:

=INT(PERCENTILE(A1:A10,0.5))

"Francisco" wrote:

I have a suggestion that we will be very useful for our company.
The way Excel calculate PERCENTILE and QUARTILE series is not appropiate for
discrete numbers.

My suggestion
At the moment =percentile(a1:a10,.05) gives 5.5 in the sequence
{1,2,3,4,5,6,7,8,9,10}. This is incorrect on discrete numbers.

Following the example above, I want to gather discrete numbers, i.e. "5" on
the series rather than "5.5". The same apply for QUARTILE formulas where a
second parameter is needed.
Add a Third parameter [optional] as follows:
- 0, default (as it currently works)
- 1, takes the inferior discrete number
- 2, takes the superior discrete number


----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc

I don't think you understand the concept of quartiles correctly. Quartiles
are NOT quarters. A quartile is any of the three values which divide the
sorted data set into four equal parts, so that each part represents 1/4th of
the sample or population.

Your range has eleven elements; accordingly 11/4 = 2.75. Thus the third
quartile is equal to (100*3/4)+2.75 or 77.5.

Neither MEDIAN nor QUARTILE calculates incorrectly; you just don't seem to
understand the terms.

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"Francisco" wrote:

Dave,
That is not entire true, there are 2 methods. That is fine for certain
cases, however when it is necessary to take a value that belongs to the
series this method is not appropriated. The key is: "it has to belong". Excel
should allow me to fine tune this.

I have another example where Excel has the WRONG calculation:
{10,20,30,40,50,60,70,80,90,100}
What do you expect if you do =quartile(RANGE,3), Upper Quartile?
Yes, you will expect 75 as the Upper Quartile .... but surprise surprise
...Excel shows 77.5.
This is just plain wrong.

What I am saying is to extend NOT to replace the funcionality to have a more
appropiate calculation.

F

"Dave F" wrote:

50255 is the median of the sequence of numbers you give. A set of six values
does not have a median exactly equal to the value of any of the numbers.
Rather, it is equal to the third largest value plus the fourth largest value
divided by two.

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"Francisco" wrote:

I see your point but this method does not work with large series.
The PERCENTILE value must be a number that belongs to the series.

We do tables of salaries and we need to show the salary that best represents
the MEDIAN or a QUARTILE. For example:
{$50000, $50001 $50010 $50500 $50501 $65000}
The right MEDIAN value is $50010 and Excel provides $50255, this is just
wrong. $50255 does not belong the series.

"BoniM" wrote:

=INT(PERCENTILE(A1:A10,0.5))

"Francisco" wrote:

I have a suggestion that we will be very useful for our company.
The way Excel calculate PERCENTILE and QUARTILE series is not appropiate for
discrete numbers.

My suggestion
At the moment =percentile(a1:a10,.05) gives 5.5 in the sequence
{1,2,3,4,5,6,7,8,9,10}. This is incorrect on discrete numbers.

Following the example above, I want to gather discrete numbers, i.e. "5" on
the series rather than "5.5". The same apply for QUARTILE formulas where a
second parameter is needed.
Add a Third parameter [optional] as follows:
- 0, default (as it currently works)
- 1, takes the inferior discrete number
- 2, takes the superior discrete number


----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc

Francisco's range had 10 elements, not 11. [If he had indeed had 11
elements, from 0 to 100 instead of from 10 to 100, then his upper quartile
would indeed have been at 75.]

Another flaw in your arithmetic, Dave, is that (100*3/4)+2.75 = 77.75, not
77.5.

One way of looking at it is as follows:
If there are n elements, numbered from 1 to n, then the 0th percentile is at
position 1 (value 10), and the 100th percentile is at position n (at 100).
The median (50th percentile) is at position (n+1)/2, hence at 55.
The upper quartile (75th percentile) is at position (3n+1)/4, hence at 77.5.

.... but there are a number of alternative methods.
--
David Biddulph

"Dave F" wrote in message
...
I don't think you understand the concept of quartiles correctly. Quartiles
are NOT quarters. A quartile is any of the three values which divide the
sorted data set into four equal parts, so that each part represents 1/4th
of
the sample or population.

Your range has eleven elements; accordingly 11/4 = 2.75. Thus the third
quartile is equal to (100*3/4)+2.75 or 77.5.

Neither MEDIAN nor QUARTILE calculates incorrectly; you just don't seem to
understand the terms.

Dave

"Francisco" wrote:

Dave,
That is not entire true, there are 2 methods. That is fine for certain
cases, however when it is necessary to take a value that belongs to the
series this method is not appropriated. The key is: "it has to belong".
Excel
should allow me to fine tune this.

I have another example where Excel has the WRONG calculation:
{10,20,30,40,50,60,70,80,90,100}
What do you expect if you do =quartile(RANGE,3), Upper Quartile?
Yes, you will expect 75 as the Upper Quartile .... but surprise surprise
...Excel shows 77.5.
This is just plain wrong.

What I am saying is to extend NOT to replace the funcionality to have a
more
appropiate calculation.

F

"Dave F" wrote:

50255 is the median of the sequence of numbers you give. A set of six
values
does not have a median exactly equal to the value of any of the
numbers.
Rather, it is equal to the third largest value plus the fourth largest
value
divided by two.

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"Francisco" wrote:

I see your point but this method does not work with large series.
The PERCENTILE value must be a number that belongs to the series.

We do tables of salaries and we need to show the salary that best
represents
the MEDIAN or a QUARTILE. For example:
{$50000, $50001 $50010 $50500 $50501 $65000}
The right MEDIAN value is $50010 and Excel provides $50255, this is
just
wrong. $50255 does not belong the series.

"BoniM" wrote:

=INT(PERCENTILE(A1:A10,0.5))

"Francisco" wrote:

I have a suggestion that we will be very useful for our company.
The way Excel calculate PERCENTILE and QUARTILE series is not
appropiate for
discrete numbers.

My suggestion
At the moment =percentile(a1:a10,.05) gives 5.5 in the sequence
{1,2,3,4,5,6,7,8,9,10}. This is incorrect on discrete numbers.

Following the example above, I want to gather discrete numbers,
i.e. "5" on
the series rather than "5.5". The same apply for QUARTILE
formulas where a
second parameter is needed.
Add a Third parameter [optional] as follows:
- 0, default (as it currently works)
- 1, takes the inferior discrete number
- 2, takes the superior discrete number


----------------
This post is a suggestion for Microsoft, and Microsoft responds
to the
suggestions with the most votes. To vote for this suggestion,
click the "I
Agree" button in the message pane. If you do not see the button,
follow this
link to open the suggestion in the Microsoft Web-based Newsreader
and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc

I am looking for a formula to work in Excel 2002/2003 that will give correct
quartile for performance info. where you can have high or low values which
are 'good' performance. The standard formulae do not work for both examples.
Also what if values are 0?

"Francisco" wrote:

I have a suggestion that we will be very useful for our company.
The way Excel calculate PERCENTILE and QUARTILE series is not appropiate for
discrete numbers.

My suggestion
At the moment =percentile(a1:a10,.05) gives 5.5 in the sequence
{1,2,3,4,5,6,7,8,9,10}. This is incorrect on discrete numbers.

Following the example above, I want to gather discrete numbers, i.e. "5" on
the series rather than "5.5". The same apply for QUARTILE formulas where a
second parameter is needed.
Add a Third parameter [optional] as follows:
- 0, default (as it currently works)
- 1, takes the inferior discrete number
- 2, takes the superior discrete number


----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc

Hi Dave F
I am trying to find a formula that will give me really accurate compound
interest calculations, where interest is calculated daily and added monthly.
I have used A=P(1+r) to power n, where A=accumulated sum, P is principal and
r=interest rate expressed as a proportion and n is the term.

Can you help, please?



"Dave F" wrote:

50255 is the median of the sequence of numbers you give. A set of six values
does not have a median exactly equal to the value of any of the numbers.
Rather, it is equal to the third largest value plus the fourth largest value
divided by two.

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"Francisco" wrote:

I see your point but this method does not work with large series.
The PERCENTILE value must be a number that belongs to the series.

We do tables of salaries and we need to show the salary that best represents
the MEDIAN or a QUARTILE. For example:
{$50000, $50001 $50010 $50500 $50501 $65000}
The right MEDIAN value is $50010 and Excel provides $50255, this is just
wrong. $50255 does not belong the series.

"BoniM" wrote:

=INT(PERCENTILE(A1:A10,0.5))

"Francisco" wrote:

I have a suggestion that we will be very useful for our company.
The way Excel calculate PERCENTILE and QUARTILE series is not appropiate for
discrete numbers.

My suggestion
At the moment =percentile(a1:a10,.05) gives 5.5 in the sequence
{1,2,3,4,5,6,7,8,9,10}. This is incorrect on discrete numbers.

Following the example above, I want to gather discrete numbers, i.e. "5" on
the series rather than "5.5". The same apply for QUARTILE formulas where a
second parameter is needed.
Add a Third parameter [optional] as follows:
- 0, default (as it currently works)
- 1, takes the inferior discrete number
- 2, takes the superior discrete number


----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc

Sorry - did not finish this query off - Using the formula below I have been
able to get close to an accurate figure for calculating interest on my
mortgage, but it is not quite right for a case where interest is calculated
daily and added monthly.

Do you have a better suggestion, please?

"Formula Fred" wrote:

Hi Dave F
I am trying to find a formula that will give me really accurate compound
interest calculations, where interest is calculated daily and added monthly.
I have used A=P(1+r) to power n, where A=accumulated sum, P is principal and
r=interest rate expressed as a proportion and n is the term.

Can you help, please?



"Dave F" wrote:

50255 is the median of the sequence of numbers you give. A set of six values
does not have a median exactly equal to the value of any of the numbers.
Rather, it is equal to the third largest value plus the fourth largest value
divided by two.

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"Francisco" wrote:

I see your point but this method does not work with large series.
The PERCENTILE value must be a number that belongs to the series.

We do tables of salaries and we need to show the salary that best represents
the MEDIAN or a QUARTILE. For example:
{$50000, $50001 $50010 $50500 $50501 $65000}
The right MEDIAN value is $50010 and Excel provides $50255, this is just
wrong. $50255 does not belong the series.

"BoniM" wrote:

=INT(PERCENTILE(A1:A10,0.5))

"Francisco" wrote:

I have a suggestion that we will be very useful for our company.
The way Excel calculate PERCENTILE and QUARTILE series is not appropiate for
discrete numbers.

My suggestion
At the moment =percentile(a1:a10,.05) gives 5.5 in the sequence
{1,2,3,4,5,6,7,8,9,10}. This is incorrect on discrete numbers.

Following the example above, I want to gather discrete numbers, i.e. "5" on
the series rather than "5.5". The same apply for QUARTILE formulas where a
second parameter is needed.
Add a Third parameter [optional] as follows:
- 0, default (as it currently works)
- 1, takes the inferior discrete number
- 2, takes the superior discrete number


----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc

On Jun 25, 2:41 pm, Formula Fred
wrote:
Sorry - did not finish this query off - Using the formula below I have been
able to get close to an accurate figure for calculating interest on my
mortgage, but it is not quite right for a case where interest is calculated
daily and added monthly. Do you have a better suggestion, please?

"Formula Fred" wrote:
I am trying to find a formula that will give me really accurate compound
interest calculations, where interest is calculated daily and added monthly.
I have used A=P(1+r) to power n, where A=accumulated sum, P is principal
and r=interest rate expressed as a proportion and n is the term.
Can you help, please?

It would behoove you to post this inquiry in a new thread with an
appropriate subject. This inquiry has nothing to do with percentile
and quartile formulas.

And if/when you repost appropriately, I suggest that you include
details that explain why you think your computation is "not quite
right" -- that is, specific numbers from statements from your mortgage
company or textbook.

Of course, your formula is more than a little "not quite right" for a
mortgage problem, having little to do with whether interest is
"calculated daily and added monthly". But just how simple or complex
a formula you need depends on your refining your question. The more
specifics that you can provide, the better we can answer your question

I defer the details to the new thread that I hope you will start.

Thank you for replying.
In answer to your rebuke, I am new to this site and could not find a way of
posting a new discussion, so I tagged it onto an old one, as I did have
another separate query relating to quartiles.
This should be the correct formula for calculating mortgage interest, but
slight refinement may be needed. You are right - it does not compare exactly
with my mortgage statement. The trouble is that I do not know what further
information I could supply beyond the fact that interest is calculated daily
and added monthly, so perhaps the calculation needs to be adjusted so that it
uses a daily interest rate and n would =365?
This must be a universal problem that many people have encountered before
and there should be some standard formulae somewhere, shouldn't there?
I cannot find it in Microsoft's own Excel book, though, or in any Help menu.

Thanks, in anticipation.

"joeu2004" wrote:

On Jun 25, 2:41 pm, Formula Fred
wrote:
Sorry - did not finish this query off - Using the formula below I have been
able to get close to an accurate figure for calculating interest on my
mortgage, but it is not quite right for a case where interest is calculated
daily and added monthly. Do you have a better suggestion, please?

"Formula Fred" wrote:
I am trying to find a formula that will give me really accurate compound
interest calculations, where interest is calculated daily and added monthly.
I have used A=P(1+r) to power n, where A=accumulated sum, P is principal
and r=interest rate expressed as a proportion and n is the term.
Can you help, please?

It would behoove you to post this inquiry in a new thread with an
appropriate subject. This inquiry has nothing to do with percentile
and quartile formulas.

And if/when you repost appropriately, I suggest that you include
details that explain why you think your computation is "not quite
right" -- that is, specific numbers from statements from your mortgage
company or textbook.

Of course, your formula is more than a little "not quite right" for a
mortgage problem, having little to do with whether interest is
"calculated daily and added monthly". But just how simple or complex
a formula you need depends on your refining your question. The more
specifics that you can provide, the better we can answer your question

I defer the details to the new thread that I hope you will start.

On Jun 25, 11:27 pm, Formula Fred
wrote:
In answer to your rebuke

It was not intended to be a "rebuke" so much as a constructive
suggestion. My point is: by burying this inquiry in an unrelated
thread, you might get less participation in the discussion and,
therefore, less of an answer.

I am new to this site and could not find a way of posting a new
discussion, so I tagged it onto an old one

That is really a poor excuse. If you don't know how to do it
correctly, don't do it incorrectly. Get some help.

This should be the correct formula for calculating mortgage interestj

Not at all! It is a formula for computing the effect of compounding
interest in a savings account. A=P*(1+r)^n, as you wrote it, results
in a future value (A) that is larger than the principal. But of
course, for a mortgage calculation, the future value is declining --
one would hope ;-).

The discrete math formula can be found on the PV() help page. But it
is really quite ugly and complicated to use. That's why God -- ah, MS
(really Visicalc) -- created functions.

You are right - it does not compare exactly
with my mortgage statement. The trouble is that I do not know what further
information I could supply beyond the fact that interest is calculated daily
and added monthly

Well, you start with the terms of the loan agreement: loan amount,
term of the loan, payment frequency, interest rate, payment amount
(not including PMI or any other periodic payments that are not paying
down the principal and interest). You might also mention if you are
talking about a Canadian mortgage (sigh).

Moreover, it is not clear what you mean when you say that you want
"really accurate compound interest calculations". Do you want the
interest charged in a billing period? Or do you want to compute
cumulative interest over several billing periods -- for example, the
interest charge in a year?

This must be a universal problem that many people have encountered before
and there should be some standard formulae somewhere, shouldn't there?

Part of the problem with trying to answer such an abstract question
is: the devil is in the details. There are straight-forward
answers. But often, people find they do not exactly match their
mortgage statements because reality creeps in.

For example, for 15-year loan of $100,000 at 6% with monthly payments,
the payment is ostensibly PMT(6%/12, 15*12, -100000). But that is
wrong because a lender must round at least to the smallest coin of the
realm -- for example, cents in the US. And often, lenders will round
it further -- for example, to dollars in the US. That radically
affects how interest is subsequently computed, how many payments are
made, and the amount of the last payment.

Suppose the payment is $844 -- that is, ROUNDUP(PMT(...), 0). Then,
the actual number of payments is ROUNDUP(NPER(6%/12,844,-100000),0).
In this case, that happens to be 180, the same as 15*12. But
sometimes, it is 1 or 2 periods less. In any case, the last payment
is not $844. You can estimate the last payment with ROUNDUP(FV(6%/
12,180-1,844,-100000)*(1+6%/12),0).

Note: Whether to use ROUND() or ROUNDUP() and whether to round to 0
or 2 decimal places are really up to the lender. In the US, the only
legal requirement is that the lender disclose all of this information
when the loan contract is finalized.

But all of that is approximate because you said that interest is
computed daily, not monthly. Consequently, the exact numbers will
depend on the date when the first period begins. It might also depend
on when the payment is actually made; but let's assume that payments
are made on their due dates.

The only way I know of to compute interest "really accurately" when it
is accrued daily is to create an amortization schedule. Suppose
payments are due on the 15-th of each month, and the first period
begins on 15 Feb 2006. If A1 contains 2/15/2006, A2 contains
3/15/2006 and E1 contains the initial balance (100000), then the
interest for the first payment is (in C2):

=E1*(A2-A1)*6%/365

and the new balance is (in E2):

=E1+C2-B2

where B2 is the payment (844). Likewise, if A3 contains 4/15/2006,
the interest for the second payment is (in C3):

=E2*(A3-A2)*6%/365

Clearly, (A2-A1)*6%/365 is not necessarily the same as (A3-A2)*6%/365
for any two months. Therein lies the problem in trying to compute
interest "really accurately" when interest is computed daily: the
interest rate is not constant for every month.

Hope this intro helps. It is necessarily very brief.

Thanks, Joeu.
Yes, I think the final few paras will in fact be very useful.

"joeu2004" wrote:

On Jun 25, 11:27 pm, Formula Fred
wrote:
In answer to your rebuke

It was not intended to be a "rebuke" so much as a constructive
suggestion. My point is: by burying this inquiry in an unrelated
thread, you might get less participation in the discussion and,
therefore, less of an answer.

I am new to this site and could not find a way of posting a new
discussion, so I tagged it onto an old one

That is really a poor excuse. If you don't know how to do it
correctly, don't do it incorrectly. Get some help.

This should be the correct formula for calculating mortgage interestj

Not at all! It is a formula for computing the effect of compounding
interest in a savings account. A=P*(1+r)^n, as you wrote it, results
in a future value (A) that is larger than the principal. But of
course, for a mortgage calculation, the future value is declining --
one would hope ;-).

The discrete math formula can be found on the PV() help page. But it
is really quite ugly and complicated to use. That's why God -- ah, MS
(really Visicalc) -- created functions.

You are right - it does not compare exactly
with my mortgage statement. The trouble is that I do not know what further
information I could supply beyond the fact that interest is calculated daily
and added monthly

Well, you start with the terms of the loan agreement: loan amount,
term of the loan, payment frequency, interest rate, payment amount
(not including PMI or any other periodic payments that are not paying
down the principal and interest). You might also mention if you are
talking about a Canadian mortgage (sigh).

Moreover, it is not clear what you mean when you say that you want
"really accurate compound interest calculations". Do you want the
interest charged in a billing period? Or do you want to compute
cumulative interest over several billing periods -- for example, the
interest charge in a year?

This must be a universal problem that many people have encountered before
and there should be some standard formulae somewhere, shouldn't there?

Part of the problem with trying to answer such an abstract question
is: the devil is in the details. There are straight-forward
answers. But often, people find they do not exactly match their
mortgage statements because reality creeps in.

For example, for 15-year loan of $100,000 at 6% with monthly payments,
the payment is ostensibly PMT(6%/12, 15*12, -100000). But that is
wrong because a lender must round at least to the smallest coin of the
realm -- for example, cents in the US. And often, lenders will round
it further -- for example, to dollars in the US. That radically
affects how interest is subsequently computed, how many payments are
made, and the amount of the last payment.

Suppose the payment is $844 -- that is, ROUNDUP(PMT(...), 0). Then,
the actual number of payments is ROUNDUP(NPER(6%/12,844,-100000),0).
In this case, that happens to be 180, the same as 15*12. But
sometimes, it is 1 or 2 periods less. In any case, the last payment
is not $844. You can estimate the last payment with ROUNDUP(FV(6%/
12,180-1,844,-100000)*(1+6%/12),0).

Note: Whether to use ROUND() or ROUNDUP() and whether to round to 0
or 2 decimal places are really up to the lender. In the US, the only
legal requirement is that the lender disclose all of this information
when the loan contract is finalized.

But all of that is approximate because you said that interest is
computed daily, not monthly. Consequently, the exact numbers will
depend on the date when the first period begins. It might also depend
on when the payment is actually made; but let's assume that payments
are made on their due dates.

The only way I know of to compute interest "really accurately" when it
is accrued daily is to create an amortization schedule. Suppose
payments are due on the 15-th of each month, and the first period
begins on 15 Feb 2006. If A1 contains 2/15/2006, A2 contains
3/15/2006 and E1 contains the initial balance (100000), then the
interest for the first payment is (in C2):

=E1*(A2-A1)*6%/365

and the new balance is (in E2):

=E1+C2-B2

where B2 is the payment (844). Likewise, if A3 contains 4/15/2006,
the interest for the second payment is (in C3):

=E2*(A3-A2)*6%/365

Clearly, (A2-A1)*6%/365 is not necessarily the same as (A3-A2)*6%/365
for any two months. Therein lies the problem in trying to compute
interest "really accurately" when interest is computed daily: the
interest rate is not constant for every month.

Hope this intro helps. It is necessarily very brief.

Actually, though, you know, my method for calculating interest does work with
mortgages. You just apply the interest to the loan amount to reduce the
balance, of course - the principle is exactly the same.
Rounding would affect the result - I'm aware of that, but this was not the
issue.
Your method may well do the trick.
I'll let you know whether it gives a result closer to the mortgage statement
figures than the one I was originally using!

"joeu2004" wrote:

On Jun 25, 11:27 pm, Formula Fred
wrote:
In answer to your rebuke

It was not intended to be a "rebuke" so much as a constructive
suggestion. My point is: by burying this inquiry in an unrelated
thread, you might get less participation in the discussion and,
therefore, less of an answer.

I am new to this site and could not find a way of posting a new
discussion, so I tagged it onto an old one

That is really a poor excuse. If you don't know how to do it
correctly, don't do it incorrectly. Get some help.

This should be the correct formula for calculating mortgage interestj

Not at all! It is a formula for computing the effect of compounding
interest in a savings account. A=P*(1+r)^n, as you wrote it, results
in a future value (A) that is larger than the principal. But of
course, for a mortgage calculation, the future value is declining --
one would hope ;-).

The discrete math formula can be found on the PV() help page. But it
is really quite ugly and complicated to use. That's why God -- ah, MS
(really Visicalc) -- created functions.

You are right - it does not compare exactly
with my mortgage statement. The trouble is that I do not know what further
information I could supply beyond the fact that interest is calculated daily
and added monthly

Well, you start with the terms of the loan agreement: loan amount,
term of the loan, payment frequency, interest rate, payment amount
(not including PMI or any other periodic payments that are not paying
down the principal and interest). You might also mention if you are
talking about a Canadian mortgage (sigh).

Moreover, it is not clear what you mean when you say that you want
"really accurate compound interest calculations". Do you want the
interest charged in a billing period? Or do you want to compute
cumulative interest over several billing periods -- for example, the
interest charge in a year?

This must be a universal problem that many people have encountered before
and there should be some standard formulae somewhere, shouldn't there?

Part of the problem with trying to answer such an abstract question
is: the devil is in the details. There are straight-forward
answers. But often, people find they do not exactly match their
mortgage statements because reality creeps in.

For example, for 15-year loan of $100,000 at 6% with monthly payments,
the payment is ostensibly PMT(6%/12, 15*12, -100000). But that is
wrong because a lender must round at least to the smallest coin of the
realm -- for example, cents in the US. And often, lenders will round
it further -- for example, to dollars in the US. That radically
affects how interest is subsequently computed, how many payments are
made, and the amount of the last payment.

Suppose the payment is $844 -- that is, ROUNDUP(PMT(...), 0). Then,
the actual number of payments is ROUNDUP(NPER(6%/12,844,-100000),0).
In this case, that happens to be 180, the same as 15*12. But
sometimes, it is 1 or 2 periods less. In any case, the last payment
is not $844. You can estimate the last payment with ROUNDUP(FV(6%/
12,180-1,844,-100000)*(1+6%/12),0).

Note: Whether to use ROUND() or ROUNDUP() and whether to round to 0
or 2 decimal places are really up to the lender. In the US, the only
legal requirement is that the lender disclose all of this information
when the loan contract is finalized.

But all of that is approximate because you said that interest is
computed daily, not monthly. Consequently, the exact numbers will
depend on the date when the first period begins. It might also depend
on when the payment is actually made; but let's assume that payments
are made on their due dates.

The only way I know of to compute interest "really accurately" when it
is accrued daily is to create an amortization schedule. Suppose
payments are due on the 15-th of each month, and the first period
begins on 15 Feb 2006. If A1 contains 2/15/2006, A2 contains
3/15/2006 and E1 contains the initial balance (100000), then the
interest for the first payment is (in C2):

=E1*(A2-A1)*6%/365

and the new balance is (in E2):

=E1+C2-B2

where B2 is the payment (844). Likewise, if A3 contains 4/15/2006,
the interest for the second payment is (in C3):

=E2*(A3-A2)*6%/365

Clearly, (A2-A1)*6%/365 is not necessarily the same as (A3-A2)*6%/365
for any two months. Therein lies the problem in trying to compute
interest "really accurately" when interest is computed daily: the
interest rate is not constant for every month.

Hope this intro helps. It is necessarily very brief.

Mea culpa....

On Jun 25, 2:34 pm, Formula Fred
wrote:
I am trying to find a formula that will give me really accurate compound
interest calculations, where interest is calculated daily and added monthly.
I have used A=P(1+r) to power n, where A=accumulated sum, P is principal
and r=interest rate expressed as a proportion and n is the term.

On Jun 25, 11:27 pm, Formula Fred
wrote:
This should be the correct formula for calculating mortgage interest

On Jun 26, 2:02 am, I wrote:
Not at all! It is a formula for computing the effect of compounding
interest in a savings account.

Perhaps I should not have been so dismissive. If your formula is
intended to compute the balance, including interest, at the end of a
__period__ before applying the payment, and n is the number of days in
the __period__ (not "the term", which I interpreted to mean the term
of the loan), then your formula is merely a different interpretation
of "interest calculated daily and added monthly".

At issue is __how__ interest is calculated daily during a payment
period. That should be specified in the loan agreement.

Your formula reflects compounding daily. My formula reflects simple
interest.
In my example (15-year loan of $100,000 at 6% paid monthly), the
difference per period is very small -- $1.02 to $1.35 in interest.
That might explain why you believe your formula needs only "slight
refinement".

I have seen both used methods for savings and money market accounts.
I have only seen the latter method (simple interest) used for loans
that compute interest daily.

If mortgage interest were compounded daily, either the payment should
be computed differently, or the last payment might be larger than the
others, which should be disclosed in the loan agreement. In my
example, the last payment would be about $1039, compared to $844
normally. Alternativey, a monthly payment of $845 (rounded to the
dollar) would be sufficient to avoid a larger last payment.

Hi,
Thanks.
Yes, i know my formula can be used to calculate the interest on a mortgage,
it's just a question of refinement, I think.
But in the UK all interest (savings and mortgage) is compound not simple, so
it is compounded daily and added monthly, which is why my mortgage is
reducing so very slowly!!

I also posted a query about Quartiles - do you have any ideas on this one,
please?


"joeu2004" wrote:

Mea culpa....

On Jun 25, 2:34 pm, Formula Fred
wrote:
I am trying to find a formula that will give me really accurate compound
interest calculations, where interest is calculated daily and added monthly.
I have used A=P(1+r) to power n, where A=accumulated sum, P is principal
and r=interest rate expressed as a proportion and n is the term.

On Jun 25, 11:27 pm, Formula Fred
wrote:
This should be the correct formula for calculating mortgage interest

On Jun 26, 2:02 am, I wrote:
Not at all! It is a formula for computing the effect of compounding
interest in a savings account.

Perhaps I should not have been so dismissive. If your formula is
intended to compute the balance, including interest, at the end of a
__period__ before applying the payment, and n is the number of days in
the __period__ (not "the term", which I interpreted to mean the term
of the loan), then your formula is merely a different interpretation
of "interest calculated daily and added monthly".

At issue is __how__ interest is calculated daily during a payment
period. That should be specified in the loan agreement.

Your formula reflects compounding daily. My formula reflects simple
interest.
In my example (15-year loan of $100,000 at 6% paid monthly), the
difference per period is very small -- $1.02 to $1.35 in interest.
That might explain why you believe your formula needs only "slight
refinement".

I have seen both used methods for savings and money market accounts.
I have only seen the latter method (simple interest) used for loans
that compute interest daily.

If mortgage interest were compounded daily, either the payment should
be computed differently, or the last payment might be larger than the
others, which should be disclosed in the loan agreement. In my
example, the last payment would be about $1039, compared to $844
normally. Alternativey, a monthly payment of $845 (rounded to the
dollar) would be sufficient to avoid a larger last payment.

On Jun 26, 2:27 pm, Formula Fred
wrote:
But in the UK all interest (savings and mortgage) is compound not simple,
so it is compounded daily and added monthly

Oh, that is useful information. Unfortunately, I am not familiar with
UK mortgages per se. If interest compounds daily, I wonder how the
amount of monthly payment and daily interest rate are computed. I
will look around for an online UK mortgage calculator.

(This exemplifies why you should post this inquiry in a new thread
with an appropriate subject. Then it is likely to be noticed by other
participants, some of whom I know are UK residents who might be
familiar with vagaries of UK mortgates.)

In the meantime, let me amend my previous formulas to reflect daily
compounding. They might still not agree with UK computations because
I am making a huge assumption about how the daily interest rate is
computed. But at least it might provide you with an appropriate
paradigm into which you can plug in the appropriate numbers, once you
learn what they are.

Consider a 15-year loan of $100,000 with a fixed (nominal) interest
rate of 6% and monthly payments. For now, assume the monthly payment
is $844.

Previously, I wrote:
Suppose payments are due on the 15-th of each month,
and the first period begins on 15 Feb 2006. If A1 contains
2/15/2006, A2 contains 3/15/2006, and E1 contains the initial
balance (100000), then the interest for the first payment is (in C2):
=E1*(A2-A1)*6%/365
and the new balance is (in E2):
=E1+C2-B2
where B2 is the payment (844).

For daily compounding, the formula in C2 might instead be:

=E1*(1+6%/365)^(A2-A1) - E1

The formula in E2 would remain the same.

Again, this presumes that 6% is a nominal interest rate. I wonder if
in the UK, interest rates are specified as "APRs" -- that is,
compounded ("effective") interest rates. If that is the case, replace
1+6%/365 with (1+6%)^(1/365), which can be computed once in an
auxilliary cell.

(But in that case, the payment could be reduced to $836 for 180
payments.)

On Jun 26, 8:33 pm, I wrote:
Again, this presumes that 6% is a nominal interest rate. I wonder if
in the UK, interest rates are specified as "APRs" -- that is,
compounded ("effective") interest rates. If that is the case, replace
1+6%/365 with (1+6%)^(1/365), which can be computed once in an
auxilliary cell.

Wrong! I don't know what I was smoking at the time, but that has
nothing to do with mortgage APRs.