formula

A

Amy

I am trying to work on a spreadsheet and am stuck on how to approach a
formula. I have a spreadsheet that calculates three numbers called penalty,
interest and principal. I then have to have a formula that applies a payment
to each item in a certain order. Once I receive a payment it has to be first
allocated to the penalty, then if any is left over it has to reduce the
interest, then finally it gets applied to the principal. In other words I am
looking to set a formula with specific payment allocation. I was trying to
do it with an if then statement but am very stuck any ideas
 
S

Sandy Mann

With the Penalty amount in D2, Interest amount in E2, Principal amount in F2
and the Payment in C3, see if this does what you want:

D3: =D2-MIN(D2,C3)

E3: =E2-MIN(C3-D2,E2,C3)

F3: =F2-MIN(C3-D2-E2,C3)

--
HTH

Sandy
In Perth, the ancient capital of Scotland

[email protected]
[email protected] with @tiscali.co.uk
 
M

Miguel Zapico

You can use the MIN function. For example, with names on the formulas:
Penalty: =MIN(payment,penalty)
Interest: =IF(payment>penalty,MIN(payment-penalty,interest),0)
Principal:
=IF(payment>(penalty+interest),MIN(payment-penalty-interest,principal),0)

Hope this helps,
Miguel.
 
Top