Formula That Takes Into Account Position Within a Table

O

oldbrewer

Well-known Member

Joined

Apr 11, 2010

Messages

11,012

• May 28, 2017

• #2

for item A - why is the fee not £375 in Q4 and Q5

 

Upvote 0

D

DasMort

New Member

Joined

May 27, 2017

Messages

7

• May 28, 2017

• #3

That is because Q1 = $15,000 * 0.05 * (0.65 / 2 + 0.15) and Q2 = $15,000 *0.05 * (0.65 / 2 + 0.20)

 

Upvote 0

D

DasMort

New Member

Joined

May 27, 2017

Messages

7

• May 28, 2017

• #4

DasMort said:

That is because Q1 = $15,000 * 0.05 * (0.65 / 2 + 0.15) and Q2 = $15,000 *0.05 * (0.65 / 2 + 0.20)

Click to expand...

My apologies, that's Q4 and Q5 respectively.

 

Upvote 0

O

oldbrewer

Well-known Member

Joined

Apr 11, 2010

Messages

11,012

• May 28, 2017

• #5

											1	2	3	4	5	6	7
principal	quarters	fee%	start qtr	end qtr	FEE	3 or more qtrs	startamnt	en****t	balamnt	balpayment	Q1	Q2	Q3	Q4	Q5	Q6	Q7
15000	2	5%	4	5	750	no	375	375	0	0	0	0	0	375	375	0	0
20000	3	5%	2	4	1000	yes	150	200	650	650	0	150	650	200	0	0	0
100000	5	5%	2	6	5000	yes	750	1000	3250	1083.3333	0	750	1083.33	1083.33	1083.33	1000	0
			formula in L3 (0) under Q1 is
			=IF(L$1<$D3,0,IF(L$1=$D3,$H3,IF(AND(L$1>$D3,L$1<$E3),$K3,IF(L$1=$E3,$I3,0))))

<colgroup><col><col><col><col span="2"><col><col><col><col><col><col><col span="8"></colgroup><tbody>
</tbody>

 

Upvote 0

O

oldbrewer

Well-known Member

Joined

Apr 11, 2010

Messages

11,012

• May 28, 2017

• #6

all the figures in cols E F,G,H,I,J,K are calculated automatically

oops en****t = endamount

 

Upvote 0

D

DasMort

New Member

Joined

May 27, 2017

Messages

7

• May 28, 2017

• #7

Hi oldbrewer,

Thank you for the reply! It doesn't look like the calculations are correct. For example, where the principal is 100,000, with 5 quarters, the payments should be as follows:

Q2: 1400
Q3: 650
Q4: 650
Q5: 650
Q6: 1,650
Total: 5,000

 

Upvote 0

O

oldbrewer

Well-known Member

Joined

Apr 11, 2010

Messages

11,012

• May 28, 2017

• #8

5% of 100000 is 5000
15% = 0.15
5000 multiplied by 0.15 is 750

what am I doing wrong ?

 

Upvote 0

D

DasMort

New Member

Joined

May 27, 2017

Messages

7

• May 28, 2017

• #9

The first quarter will always be: Principal * 0.05 * (0.65 / # of quarters + 0.15)
The last quarter will always be: Principal * 0.05 * (0.65 / # of quarters + 0.20)
Any middle quarters will always be: Principal * 0.05 * (0.65 / # of quarters)

 

Upvote 0

O

oldbrewer

Well-known Member

Joined

Apr 11, 2010

Messages

11,012

• May 29, 2017

• #10

											1	2	3	4	5	6	7
principal	quarters	fee%	start qtr	end qtr	FEE	3 or more qtrs	startamnt	en****t	balamnt	balpayment	Q1	Q2	Q3	Q4	Q5	Q6	Q7
15000	2	5%	4	5	750	no	375	375	0	0	0	0	0	375	375	0	0
20000	3	5%	2	4	1000	yes	366.6667	416.667	216.667	216.66667	0	366.6667	216.6667	416.6667	0	0	0
100000	5	5%	2	6	5000	yes	1400	1650	1950	650	0	1400	650.00	650.00	650.00	1650	0
			formula in L3 (0) under Q1 is
			=IF(L$1<$D3,0,IF(L$1=$D3,$H3,IF(AND(L$1>$D3,L$1<$E3),$K3,IF(L$1=$E3,$I3,0))))
			USING YOUR FORMULA FOR START AND END AMOUNTS ROW 3 IS CORRECT
			BUT IT DOES NOT GIVE AT PRESENT YOUR ANSWER FOR ROW 1

<colgroup><col><col><col><col span="2"><col><col><col><col><col><col><col span="8"></colgroup><tbody>
</tbody>

 

Upvote 0