Loan Calculator - calculated and compounding daily charged monthly including fees

Jking0191

New Member
Joined
Mar 22, 2021
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
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
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Jking0191

New Member
Joined
Mar 22, 2021
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
Loan Forecast Calculator its called I think.

Any excel experts who can help?
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,654
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
What does your spreadsheet look like? N.B. You can post an extract with the forum's XL2BB.

You could show the transactions for the first year.
What are your specific questions?
 

Jking0191

New Member
Joined
Mar 22, 2021
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
What does your spreadsheet look like? N.B. You can post an extract with the forum's XL2BB.

You could show the transactions for the first year.
What are your specific questions?

Hi Sorry,
I'm not sure on how to upload the spreadsheet. But this is what I have which is wrong as all I've done was add the fees and charges after I had calculated the FV of the standard Loan amount, Interest rate and loan length. This was also calculated monthly - which again is incorrect because i need to have it compound daily and charged monthly.
 

Attachments

  • XL.PNG
    XL.PNG
    34.6 KB · Views: 7

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,654
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

Your example does not help very much
- some of the amounts are different compared to your post #1
- showing a calculation then saying it is wrong doesn't yield a clear explanation of the correct calculation.

It is not constructive to guess about the inputs and calculation methods.
The Add-in XL2BB is not difficult to install. Click on the icon above and install.
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,654
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Is the following correct? Please review and advise.

T202103a.xlsm
ABC
1Loan100,000.00
2Establishment fee1,000.00
3Setup500.00
4Administration200.00
5Monthly fee100.00
6Interest7.2%0.00019726
7
8Principal101,700.00
911-Mar-21102,421.90
1021-Apr-21103,128.02
1131-May-21103,858.65
1241-Jun-21
5a
Cell Formulas
RangeFormula
C6C6=B6/365
C8C8=SUM(B1:B4)
C9:C11C9=C8*(B10-B9)*$C$6+100+C8
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,654
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

You will have to check the loan agreement to confirm the amounts and interest calculations.
The attached may be useful.
You can prepare a full listing of the calculations to confirm that the amounts are reasonable.

T202103a.xlsm
ABCDEFG
1Loan100,000.00
2Establishment fee1,000.00
3Setup500.00
4Administration200.00
5Monthly fee100.00
6Compliance annual fee198.0051015
7Interest7.2%0.0001973153,952.55228,748.77335,815.11
8Term5
5a
Cell Formulas
RangeFormula
C7C7=B7/365
E7E7=FV(B7/12,B8*12,0,-C10)+FV(B7/12,B8*12,-B5,0)+FV(B7,B8,-B6)
F7F7=FV(B7/12,10*12,0,-C10)+FV(B7/12,10*12,-B5,0)+FV(B7,10,-B6)
G7G7=FV(B7/12,15*12,0,-C10)+FV(B7/12,15*12,-B5,0)+FV(B7,15,-B6)
 

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
3,021
Office Version
  1. 2010
Platform
  1. Windows
It [interest] is calculated daily and charged monthly

If interest is truly "calculated daily", then the monthly rate varies based on the number of days in each month (28, 29, 30 or 31).

In that case, the only way to calculate total payments is with an amortization schedule. That also requires the loan date and the first-payment date as input.

But it is unusual for interest on loans per se to be calculated daily.

I'll assume that either you misunderstand, or you do not require that degree of accuracy.

what I have [is] wrong [....] because i need to have it compound daily and charged monthly

I suspect that you mean the annual rate is a compounded rate, calculated by compounding daily.

That is common for mortgages per se in EU countries, for example. It might not apply to other types of loans, in the UK for example.

In that case, instead of a monthly rate of 7.2%/12, it is (1 + 7.2%)^(1/12) - 1 or equivalently RATE(12, 0, -1, 1+7.2%).

The following assumes that.

(However, it is unusual for US loans to state a compounded annual rate. IIRC, the "Truth In Lending" regulations require a simple interest rate. In that case, the monthly rate is indeed 7.2%/12, and the daily rate is typically 7.2%/365, even for leap years, IIRC.)

what I have [is] wrong as all I've done was add the fees and charges after I had calculated the FV

It is correct for you to add fees and charges as you did. They are not amortized with the principal of the loan.

However, your use of FV applies to a savings of $100,000, in which interest is compounded. In contrast, for loans, interest declines over the term of the loan because it is based on outstanding principal, which declines with each payment.

The following is based the data in your posting #4, which differs significantly from your initial posting. (sigh)

Book1
CDEFGH
5$100,000.00principal
6Loan$100,000.00$699.00initial fees
7Rate7.20%annually$6,000.00service fees
8Term5years$990.00compliance fees
9Pmt$1,978.84monthly$18,730.17interest
10$126,419.17total
11
12Set-up fee$500.00initial$18,730.17CUMIPMT
13Admin fee$199.00initial
14Service fee$100.00monthly
15Compliance fee$198.00annually
Sheet1


Select or hover the cursor over cells to see formulas. In particular, the formula in G10 is:

=D9*D8*12 + SUM(D12:D13) + D14*D8*12 + D15*D8

For comparison, I show the calculaton of total interest using CUMIPMT in G12. I prefer not to use CUMIPMT for several reasons, chiefly: it calculates the periodic payment internally, which might not be realistic.

Instead, I prefer to use the payment calculated in D9, namely:

=PMT(RATE(12, 0, -1, 1+7.2%), D8*12, -D6)

That allows for real-world changes. For example, the payment must be rounded (typcially rounded up) at least to the cent. But it is common to round (up) to a multiple of $0.50 or to the dollar.
 
Last edited:

Jking0191

New Member
Joined
Mar 22, 2021
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
Apologies everyone for not explaining what I want clearly.

This time I'll try and state exactly what I am after

I am trying to create a loan calculator with the details in the Xl2bb attachment below.

The question is:

If I were to lend someone $100,000 today assuming no payments were made, What would my total forecasted Loan amount be at the end of the term.

The initial Principal includes:
Set up Fee: $500
Establishment Fee: $1000
Admin Fee: $200

which would mean the initial principal amount would be $101,700

Additional Information:
Monthly Fee of $100
Annual Compliance Fee of $198

Loan term for this example would be 5 years


Note: This is where things get tricky. The loan has an interest rate of 7.2% p.a. which is to be compounded daily and charged monthly. Given, their are no repayments being made, at the end of the month a $100 monthly fee is added to the new principal amount once monthly charges have been calculated. and then it just continues on to the next month and so on, and so on. In addition, at the end of a year we will have to add the annual compliance fee of $198 as well as the monthly fee.

The tough thing would be figuring out how to get excel to know exactly how many days are in that month to then know to add the monthly fee; and at the end of the year, excel needs to then include the annual compliance fee.

I hope that makes a little more sense for everyone.



Loan Calculator (1).xlsx
ABCDEFGHIJK
1
2LOAN CALCULATOR
3
4
5
6
7
8Loan Forecast Amount
9
10
11Loan Details
12Loan Amount$100,000
13Annual Interest rate7.20%
14Loan Start Date23/03/2021
15Loan Length (years)5
16
17
18
19
20Fees
21Set-up Fee$500.00
22Initial Establishment Fee$1,000.00
23Admin Fee$200.00
24Monthly Fee$100.00
25Annual Compliance Fee$198.00
26
Forecast 1.0
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,654
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
See post #7.

joeu2004 also provided good information.​

I will repeat that information below.
We do not know the legal rules for the calculations in your country and we do not know the exact wording in the contract.
We can help with Excel; we cannot help with contract law.

The example below shows a calculation for each month. Review the calculation and ensure that it is correct.
The FV formulas and the monthly calculation yield a similar amount; we cannot expect them to be exactly equal.

The formula reduces the number of days to 28 for February in Leap Years. Delete that part if you calculate February with 29 days.
N.B. The rate 7.2% is divided by 365 for the calculations.

T202103a.xlsm
ABCDE
1Loan100,000.00
2Establishment fee1,000.00
3Setup500.00
4Administration200.00
5Monthly fee100.00
6Compliance annual fee198.005
7Interest7.2%0.0001973153,952.55
8Term5
9153,956.49
10Principal101,700.003.94
1111-Mar-21102,421.90
1221-Apr-21103,128.01
5a
Cell Formulas
RangeFormula
C7C7=B7/365
E7E7=FV(B7/12,B8*12,0,-C10)+FV(B7/12,B8*12,-B5,0)+FV(B7,B8,-B6)
E9E9=VLOOKUP(E6*12,A11:C500,3)
E10E10=E9-E7
C10C10=SUM(B1:B4)
C11:C12C11=ROUND(C10*(B12-B11-(TEXT(B10+59,"ddmmm")="29Feb"))*$C$7+$B$5+C10,2)+(MOD(A11,12)=0)*$B$6
 

Watch MrExcel Video

Forum statistics

Threads
1,129,387
Messages
5,636,019
Members
416,892
Latest member
Bensch

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top