Which Financial Function?

[Arie Bos]

As a non-financial guy, I am struggling a bit with the following:

I want to calculate a lease-back situation:

1. amount: 800,000
2. interest: 5%
3. Years: 5 (but could vary)

So, 5% over 800,000 = 40,000. So, after 5 years, we must have paid back 800,000 + (5*40,000) = 1,000,000, I think.

Which is the correct function to use to calculate the monthly amount to have paid back the 1,000,000 after 60 months? There are so many functions in excel, but without proper understanding of financial models, I don't know which one to use for this.

Many thanks,
Arie

 

[BSALV]

you have to pay yearly 184.780€, as shown in B5, in total you paid 123.899€ interest + initial 800.000 = 923.899€
For the real calculation see right hand side.

Map1
	A	B	C	D	E	F	G	H
1	amount	800 000,00 €
2	percentage	5%			year	amount	interest	payment
3	years	5			0	800 000,00
4					1	655 220,16	40 000,00	- 184 779,84
5	yearly payment	-184 779,84 €			2	503 201,33	32 761,01	- 184 779,84
6					3	343 581,56	25 160,07	- 184 779,84
7					4	175 980,80	17 179,08	- 184 779,84
8					5	-	8 799,04	- 184 779,84
9					total		123 899,19	- 923 899,19
10
Blad1

Cell Formulas
Range	Formula
G4:G8	G4	=+F3*$B$2
H4:H8	H4	=+$B$5
G9:H9	G9	=SUM(G4:G8)
B5	B5	=PMT(B2,B3,B1)
F4:F8	F4	=+F3+G4+H4

 

[Jimmypop]

Hi...

Is this maybe what you are looking for? IPMT is Excel's interest payment function. It returns the interest amount of a loan payment in a given period, assuming the interest rate and the total amount of a payment are constant in all periods.

IPMT function in Excel - calculate interest payment on a loan

The tutorial explains the syntax and basic uses of Excel IPMT function with formula examples to calculate the interest portion of a periodic payment on a loan or mortgage.

www.ablebits.com

=IPMT(B1/12, 1, B2*12, B3)

 

[igold]

The question is a bit ambiguous as the OP first states 5 years but then says 60 months. From my experience, usually payments are made by the month. That said I get this schedule.

Amortization Schedule.xlsm
	A	B	C	D	E	F	G	H	I
1	Amount	800,000.00
2	Percentage	0.004167			Month	Amount	Interest	Payment
3	Months	60			0	800,000
4					1	788,236.35	3,333.33	-15,096.99
5	Monthly Payment	($15,096.99)			2	776,423.68	3,284.32	-15,096.99
6					3	764,561.79	3,235.10	-15,096.99
7					4	752,650.48	3,185.67	-15,096.99
8					5	740,689.53	3,136.04	-15,096.99
9					6	728,678.75	3,086.21	-15,096.99
10					7	716,617.93	3,036.16	-15,096.99
11					8	704,506.85	2,985.91	-15,096.99
12					9	692,345.31	2,935.45	-15,096.99
13					10	680,133.09	2,884.77	-15,096.99
14					11	667,869.99	2,833.89	-15,096.99
15					12	655,555.80	2,782.79	-15,096.99
16					13	643,190.29	2,731.48	-15,096.99
17					14	630,773.27	2,679.96	-15,096.99
18					15	618,304.50	2,628.22	-15,096.99
19					16	605,783.78	2,576.27	-15,096.99
20					17	593,210.89	2,524.10	-15,096.99
21					18	580,585.62	2,471.71	-15,096.99
22					19	567,907.74	2,419.11	-15,096.99
23					20	555,177.04	2,366.28	-15,096.99
24					21	542,393.29	2,313.24	-15,096.99
25					22	529,556.27	2,259.97	-15,096.99
26					23	516,665.77	2,206.48	-15,096.99
27					24	503,721.56	2,152.77	-15,096.99
28					25	490,723.41	2,098.84	-15,096.99
29					26	477,671.10	2,044.68	-15,096.99
30					27	464,564.41	1,990.30	-15,096.99
31					28	451,403.11	1,935.69	-15,096.99
32					29	438,186.97	1,880.85	-15,096.99
33					30	424,915.76	1,825.78	-15,096.99
34					31	411,589.26	1,770.48	-15,096.99
35					32	398,207.23	1,714.96	-15,096.99
36					33	384,769.44	1,659.20	-15,096.99
37					34	371,275.65	1,603.21	-15,096.99
38					35	357,725.65	1,546.98	-15,096.99
39					36	344,119.19	1,490.52	-15,096.99
40					37	330,456.03	1,433.83	-15,096.99
41					38	316,735.94	1,376.90	-15,096.99
42					39	302,958.69	1,319.73	-15,096.99
43					40	289,124.03	1,262.33	-15,096.99
44					41	275,231.73	1,204.68	-15,096.99
45					42	261,281.54	1,146.80	-15,096.99
46					43	247,273.22	1,088.67	-15,096.99
47					44	233,206.54	1,030.31	-15,096.99
48					45	219,081.25	971.69	-15,096.99
49					46	204,897.10	912.84	-15,096.99
50					47	190,653.85	853.74	-15,096.99
51					48	176,351.26	794.39	-15,096.99
52					49	161,989.07	734.80	-15,096.99
53					50	147,567.03	674.95	-15,096.99
54					51	133,084.91	614.86	-15,096.99
55					52	118,542.44	554.52	-15,096.99
56					53	103,939.38	493.93	-15,096.99
57					54	89,275.48	433.08	-15,096.99
58					55	74,550.47	371.98	-15,096.99
59					56	59,764.11	310.63	-15,096.99
60					57	44,916.14	249.02	-15,096.99
61					58	30,006.30	187.15	-15,096.99
62					59	15,034.34	125.03	-15,096.99
63					60	0.00	62.64	-15,096.99
64
65					Total		105,819.21	-905,819.21
66
Sheet1

Cell Formulas
Range	Formula
B2	B2	=0.05/12
F4:F63	F4	=+F3+G4+H4
G4:G63	G4	=+F3*$B$2
H4:H63	H4	=+$B$5
B5	B5	=PMT(B2,B3,B1)
G65:H65	G65	=SUM(G4:G63)

 

[BSALV]

@igold, good point, but is the monthly interest = the yearly/12 of also compounded ?

Map1
	A	B	C	D
1		800000	800000	800000
2		5,00%	0,417%	0,407%
3		5	60	60
4		-184 779,84 €	-15 096,99 €	-15 056,32 €
5	total paid	-923 899,19 €	-905 819,21 €	-903 379,12 €
6				-2 440,09 €
Blad1

Cell Formulas
Range	Formula
D2	D2	=POWER(1+B2,1/12)-1
C2	C2	=+B2/12
C3	C3	=+B3*12
B4:D4	B4	=PMT(B2,B3,B1)
B5:D5	B5	=+B4*B3
D6	D6	=+C5-D5

 

[Arie Bos]

BSALV, JimmyPop and iGold, Thank you all for your efforts and your very clear answers. Much appreciated!

We will pay per month, so the amount of 15,096,99 seems correct to me.