Loan Calculation model

Zhakenti

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

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

please help people.

thank you.
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
3,023
Office Version
  1. 2010
Platform
  1. Windows
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
Joined
Apr 4, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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.

i think the formula is off. please help. i am not sure.
 

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
3,023
Office Version
  1. 2010
Platform
  1. Windows
i think the formula is off. please help. i am not sure.

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

Well-known Member
Joined
Mar 2, 2014
Messages
3,023
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Apr 4, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Thank you Joeu, could you please check the Interest? when i use an online tool to calculate the interest it comes up different.
here is the link:
 

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
3,023
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Mar 2, 2014
Messages
3,023
Office Version
  1. 2010
Platform
  1. Windows
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
Joined
Apr 4, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
sorry Joeu, i am from ukraine.
i didnt understand the questions.
thank you very miuch for your time and effort.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,750
Messages
5,638,133
Members
417,010
Latest member
jnuss03

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