Calculating months based on Balance amount vs monthly deduction

[fayez_MrExcel]

Im tring to get the no. of months/days AND years/months from Balance amount vs monthly deduction. But the 2 decimal result seems not correct.

• In D6 im expecting 145 months and 7 days, while
• in D7, im expecting 12 years & 9months

Book5
	C	D	E
3	Details	Value	Expected Result
4	Monthly Deduction	5,279.82
5	Amortizaton Balance	754,881.18
6	No. of Months/days	143.67	145.07
7	No. of Years / Months	12.85	12.09
Sheet1

Cell Formulas
Range	Formula
D6	D6	=IF(MOD((D5/D4),1)>0.3,(D5/D4)+1-0.3,(D5/D4))
D7:E7	D7	=IF(MOD((D6/12),1)>0.11,(D6/12)+1-0.12,(D6/12))

Just would like to clarify my query, the quotient of 143.67 represent months days. Then i'd like to convert this integer into years & months

 

[6StringJazzer]

How are you calculating your "expected result" in column E?

 

[fayez_MrExcel]

How are you calculating your "expected result" in column E?

id like to convert an integer into years and months if the number represents months and days. Example 143.67 (where 143 is a month and .67 is days)

 

[Dave Patton]

T202301a.xlsm
	A	B	C	D
1	Details	Value
2	Monthly Deduction	5,279.82
3	Amortizaton Balance	754,881.18
4	No. of Months/days	142.974795	29	142 Months 29 days
5	No. of Years / Months	11.9145662	0.91456622	11 Years 10.8 Months
6
1b

Cell Formulas
Range	Formula
B4	B4	=B3/B2
C4	C4	=INT(MOD(B4,1)*30)
D4	D4	=INT(B4)&" Months "&INT(MOD(B4,1)*30)&" days"
B5	B5	=B4/12
C5	C5	=MOD(B5,1)
D5	D5	=INT(B5)&" Years "&ROUND(MOD(B5,1),1)*12&" Months"

 

[6StringJazzer]

id like to convert an integer into years and months if the number represents months and days. Example 143.67 (where 143 is a month and .67 is days)

Maybe my question wasn't clear. What is the arithmetic you used to get those answers?