# Loan Calculation model

#### Zhakenti

##### New Member
Loan Amount: 2016042 \$
annual interest rate: 6%
Loan period in years: 7
start date of loan: 01/05/2021

6 months payment: calculate (payments are done in 6 months schedule)
number of payments: 14
total interest: calculate
total cost of loan: calculate

i need an excel sheet with a table (payment date, beginning balance, payment, principal, interest, ending balance) underneath above shown values.

thank you.

### Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

#### joeu2004

##### Banned user
The question is tricky because of a number of omitted details.

1. How is semi-annual interest calculated: cumulative daily interest; compound daily interest (!); cumulative monthly interest (!); compound monthly interest (!!); or semi-annually?

2. How is the annual rate converted to the rate that is commensurate with the calculation frequency? Often, rate/365, rate/12 or rate/2. But in many countries, (1+rate)^(1/f) - 1, where "f" is 365, 12 or 2. And then there is Canada (sigh).

3. What type of loan is this: mortgage, or something else. The choice might might affect the sub-annual rate calculation in some countries.

4. Is the start date Jan 5 or May 1? The detail might affect the sub-annual rate calculation, depending the terms of the loan.

5. What is the semi-annual payment? Ostensibly, we can use the PMT function. But in the real-world, that is always rounded: at least to the "cent"; often, to less precision (e.g. the "dollar").

Total cost is simple: principal + total intererst.

If interest is calculated and paid semi-annually, total interest can be calculated directly, namely: 14*payment - principal.

-----

i need an excel sheet with a table (payment date, beginning balance, payment, principal, interest, ending balance) underneath above shown values.

Perhaps something like the following, making some assumptions about the answers to the questions above.

Select or hover cursor over cells to see formulas. Use XL2BB scroll bar on the right. To copy into a new worksheet, click the Copy icon under "f(x)", and paste into A1.

Last edited:

#### Zhakenti

##### New Member
not quite right no?
total interest is high.
annual rate of interest is 6%
loan is for 7 years with 6 months payment in each year.

#### joeu2004

##### Banned user

Right. I just realized that myself. But I'm not sure we agree on what is off.

I believe the periodic and total interest are correct. Sum column E.

But the dated are wrong. Change the formula in B12 to =EDATE(\$B\$2,6*A12) and copy down the column.

#### joeu2004

##### Banned user
I believe the periodic and total interest are correct. Sum column E.

I should say: for the assumptions that I had to make. You have not answered any of my questions.

#### Zhakenti

##### New Member
Thank you Joeu, could you please check the Interest? when i use an online tool to calculate the interest it comes up different.

#### joeu2004

##### Banned user
2. How is the annual rate converted to the rate that is commensurate with the calculation frequency? Often, rate/365, rate/12 or rate/2. But in many countries, (1+rate)^(1/f) - 1, where "f" is 365, 12 or 2. And then there is Canada (sigh).

The link that you provided shows \$475,140.61 for total interest.

The "calculator" that I provided (response #2) shows the same result if we replace the formula in C4 with =(1+B4)^(1/2)-1 -- an option that I mentioned already.

Apparently, that is what calculator.net means by choosing the option Compound "Annually (APY)", as you did.

However, that is not correct for a US loan that conforms to the "Truth In Lending" regulations. For such US loans, the interest rate should be stated as a __simple__ annual rate, not a compounded annual rate (APY).

Since calculator.net is based in Texas (US), I find it odd that it does not at least provide an option for specifying a simple annual rate. Perhaps it does, and I'm not seeing it.

Oddly, we do get the same total interest with calculator.net that I calculated originally -- \$482,577.45 with =B4/2 in C4 -- by choosing the option Compound Semi-Annually at calculator.net.

Using common-sense interpretation of English, I would have expected the results to be just the opposite for the two "compounding" options.

I should say: for the assumptions that I had to make. You have not answered any of my questions.

And you __still__ have not done so. I expect you to answer those questions based the terms of the actual (real-life) loan.

Since you cannot follow directions, this will be my last comment on the subject. Good luck!

#### joeu2004

##### Banned user
PS....
Oddly, we do get the same total interest [...] by choosing the option Compound Semi-Annually at calculator.net. Using common-sense interpretation of English, I would have expected the results to be just the opposite for the two "compounding" options.

OTOH, their terminology is consistent with Canadian terminology -- which never made sense to me. And sure enough, using calculator.net, we can calculate the terms of a Canadian loan with monthly payments by choosing Compound Semi-Annually and Pay Back Every Month. For my Excel design (posting #2), change C4 to =(1+B4/2)^(1/6)-1 (!!) and C5 to =12*B5.

#### Zhakenti

##### New Member
sorry Joeu, i am from ukraine.
i didnt understand the questions.
thank you very miuch for your time and effort.

Replies
2
Views
100
Replies
0
Views
66
Replies
4
Views
695
Replies
8
Views
548
Replies
0
Views
144

1,181,680
Messages
5,931,377
Members
436,788
Latest member
er19

### 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.

### Which adblocker are you using?

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

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