Loan Calculator - calculated and compounding daily charged monthly including fees

[Jking0191]

Hi,

I am struggling to create a loan calculator where it takes in to account different values/fees that get added on a monthly/yearly basis. This probably makes no sense at all. but here's a break down of what I am trying to create using the info below.

If I lend someone today a loan for:
$100,000

1. Comes with an initital establishment fee of $1000
2. set up fee of $500
3. admin fee of $200

Each month they are charged:

1. monthly fee of $100
2. an interest that is:
3. 7.2% per annum
4. It is calculated daily and charged monthly

Each year they are charged

1. Annual compliance fee: $198

At the intervals of:

1. 5 years
2. 10 years
3. 15 years

What do they owe me?
Please note: This would be calculated based on the assumption that a person makes no payments at all.

Thank you

 

[Dave Patton]

try

T202103a.xlsm
	A	B	C	D	E
1	Loan	100,000.00
2	Establishment fee	1,000.00
3	Setup	500.00
4	Administration	200.00
5	Monthly fee	100.00
6	Compliance annual fee	198.00			5
7	Interest	7.2%	0.0001973		153,952.55
8	Term	5
9					153,986.03
10	Principal		101,700.00		33.48
11	1	23-Mar-21	102,421.90
12	2	23-Apr-21	103,128.01
5a

Cell Formulas
Range	Formula
C7	C7	=B7/365
E7	E7	=FV(B7/12,B8*12,0,-C10)+FV(B7/12,B8*12,-B5,0)+FV(B7,B8,-B6)
E9	E9	=VLOOKUP(E6*12,A11:C500,3)
E10	E10	=E9-E7
C10	C10	=SUM(B1:B4)
C11:C12	C11	=ROUND(C10*(B12-B11)*$C$7+$B$5+C10,2)+(MOD(A11,12)=0)*$B$6
B12	B12	=EDATE(B11,1)

 

[Dave Patton]

a little cleaner

T202103a.xlsm
	A	B	C	D
1	Loan	$100,000.00
2	Establishment fee	$1,000.00
3	Setup	$500.00
4	Administration	$200.00
5	Monthly fee	$100.00
6	Compliance annual fee	$198.00
7	Interest	7.2%	0.0001973	0.074424
8	Term	5
9
10	Principal	23-Mar-21	101,700.00
11	1	23-Apr-21	102,421.90
12	2	23-May-21	103,128.01
5a

Cell Formulas
Range	Formula
C7	C7	=B7/365
D7	D7	=(1+B7/12)^12-1
C10	C10	=SUM(B1:B4)
B11:B12	B11	=EDATE(B10,1)
C11:C12	C11	=ROUND(C10+C10*(B11-B10)*$C$7+$B$5+(MOD(A11,12)=0)*$B$6,2)