Here's a good one!

[Chris Berry]

I am trying to split loan payments between principal and interest among various sources, however due to rounding the total principal and interest exceeds the total payment. What is the easiest way to adjust either the largest principal or interest field based on which type is over. I am attaching a file that contains all the formulas and such.

I've been around and around with this and just when I think I have it, I get a circular error message or something equally as tiresome

Any help anyone can give me will be greatly apprecated.

For instance, there is both principle and interest being calculated based on deferral, match, discretionary, etc... depending on how the loan was withdrawn from the account originally.

I thought maybe using an if with a max might work, but it won't calculate. It says I still have a circular reference.

[Bernie Deitrick]

Chris,

It's likely that nobody will open your attachment.

Use

=PMT()

to calculate the payment, then use

=IPMT()

to calculate the interest, and for the principal, subtract the two.

HTH,
Bernie
MS Excel MVP


"Chris Berry" <[email protected]> wrote in message
news:[email protected]...
>
> I am trying to split loan payments between principal and interest among
> various sources, however due to rounding the total principal and
> interest exceeds the total payment. What is the easiest way to adjust
> either the largest principal or interest field based on which type is
> over. I am attaching a file that contains all the formulas and such.
>
> I've been around and around with this and just when I think I have it,
> I get a circular error message or something equally as tiresome
>
> Any help anyone can give me will be greatly apprecated.
>
> For instance, there is both principle and interest being calculated
> based on deferral, match, discretionary, etc... depending on how the
> loan was withdrawn from the account originally.
>
> I thought maybe using an if with a max might work, but it won't
> calculate. It says I still have a circular reference.
>
>
> +-------------------------------------------------------------------+
> |Filename: Forum Assistance.zip |
> |Download: http://www.excelforum.com/attachment.php?postid=5024 |
> +-------------------------------------------------------------------+
>
> --
> Chris Berry
> ------------------------------------------------------------------------
> Chris Berry's Profile: http://www.excelforum.com/member.php...o&userid=36165
> View this thread: http://www.excelforum.com/showthread...hreadid=561084
>

[Chris Berry]

Thanks for your reply. The principal and interest are known, the problem I am having is someone will have a $100 dollar payment split so that 25% or 33% or any other percentage goes into different accounts. For instance:

Principal Int #1 Prin #1 Int #2 Prin #2 Int Total
96.33 3.67 72.25 2.75 24.08 0.92 100
96.34 3.66 72.26 2.75 24.09 0.92 100.02

As you can see, both of the above examples made the same total payment, but because of the way excel calculates, I'm getting pennies rounding errors.

I can't believe that I'm the only one who has ever had this problem, but darned if I can find a solution. If there were only two accounts or if everyone had at least one common account I could do it with arithmetic, but they have to make life hard for me.

[Chris Berry]

I got the solution, albeit in a very roundabout way.

[Bernie Deitrick]

Chris,

Then, instead, for the cell with the 24.08, use the formula
=96.33-72.25
(but with cell references not values), like
=A2-C2

OR

=ROUND(A2,2)-ROUND(C2,2)

and the same for the interest...
=3.67-2.75

That will correct both calcs...

HTH,
Bernie
MS Excel MVP


"Chris Berry" <[email protected]> wrote in message
news:[email protected]...
>
> Thanks for your reply. The principal and interest are known, the
> problem I am having is someone will have a $100 dollar payment split so
> that 25% or 33% or any other percentage goes into different accounts.
> For instance:
>
> Principal Int #1 Prin #1 Int #2 Prin #2 Int Total
> 96.33 3.67 72.25 2.75 24.08 0.92 100
> 96.34 3.66 72.26 2.75 24.09 0.92 100.02
>
> As you can see, both of the above examples made the same total payment,
> but because of the way excel calculates, I'm getting pennies rounding
> errors.
>
> I can't believe that I'm the only one who has ever had this problem,
> but darned if I can find a solution. If there were only two accounts
> or if everyone had at least one common account I could do it with
> arithmetic, but they have to make life hard for me.
>
>
> --
> Chris Berry
> ------------------------------------------------------------------------
> Chris Berry's Profile: http://www.excelforum.com/member.php...o&userid=36165
> View this thread: http://www.excelforum.com/showthread...hreadid=561084
>