Loan Calculation model

[Zhakenti]

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.

 

[joeu2004]

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.

Book1
	A	B	C	D	E	F
1		Annual	Semi-Annual
2	Date	1-May-2021
3	Loan	$2,016,042.00
4	Rate	6.00%	3.00%
5	Term	7	14
6	Payment		$178,472.82	unrounded!
7	Total interest		$482,577.45
8	Total cost		$2,498,619.45
9
10	Payment#	Date	Payment	Principal	Interest	End Balance
11		1-May-2021				$2,016,042.00
12	1	1-Jun-2021	$178,472.82	$117,991.56	$60,481.26	$1,898,050.44
13	2	1-Jul-2021	$178,472.82	$121,531.30	$56,941.51	$1,776,519.14
14	3	1-Aug-2021	$178,472.82	$125,177.24	$53,295.57	$1,651,341.89
15	4	1-Sep-2021	$178,472.82	$128,932.56	$49,540.26	$1,522,409.33
16	5	1-Oct-2021	$178,472.82	$132,800.54	$45,672.28	$1,389,608.80
17	6	1-Nov-2021	$178,472.82	$136,784.55	$41,688.26	$1,252,824.24
18	7	1-Dec-2021	$178,472.82	$140,888.09	$37,584.73	$1,111,936.15
19	8	1-Jan-2022	$178,472.82	$145,114.73	$33,358.08	$966,821.42
20	9	1-Feb-2022	$178,472.82	$149,468.17	$29,004.64	$817,353.25
21	10	1-Mar-2022	$178,472.82	$153,952.22	$24,520.60	$663,401.02
22	11	1-Apr-2022	$178,472.82	$158,570.79	$19,902.03	$504,830.24
23	12	1-May-2022	$178,472.82	$163,327.91	$15,144.91	$341,502.33
24	13	1-Jun-2022	$178,472.82	$168,227.75	$10,245.07	$173,274.58
25	14	1-Jul-2022	$178,472.82	$173,274.58	$5,198.24	$0.00
Sheet1

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.

 

[Zhakenti]

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]

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]

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]

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:

Loan Calculator

Free loan calculator to find the repayment plan, interest cost, and amortization schedule of conventional amortized loans, deferred payment loans, and bonds.

www.calculator.net

 

[joeu2004]

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]

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]

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