Loan repayment calculation

[BrutalLogiC]

Hi I want to take out a loan for 25,000,000 at 7% annual fixed interest rate and make month end monthly repayments.

I want to take out the loan immediately "month 0" but not start paying it back until month 7... because I won't have any income for 6 months. I must pay the whole lot back by month 38.

I'm struggling with how to plot this out what my monthly repayments would be on a spreadsheet, can anyone help please.

The running balance of the loan would be something like this:
Month 0 = 25,000,000
Month 1 = 25,145,833
Month 2 = month 1 value + interest
Month 3 = month 2 value + interest
Month 4 = month 3 value + interest
Month 5 = month 4 value + interest
Month 6 = month 5 value + interest
Month 7 = month 6 value + interest less my first repayment
Month 8 = month 7 value + interest less my second repayment
Start of month 39 = nil!

 

[sykes]

PMT? https://exceljet.net/formula/calculate-payment-for-a-loan

 

[steve the fish]

Use goal seek. I presume you want equal payments each month save the first 6 months. Im no finance man but id have thought you need to have a column of month numbers then a formula next to them similar to:

=B8+(B8*7%/12)-$D$3

The first 6 months just dont include the -$D$3

=B2+(B2*7%/12)

where D3 is to house the monthly payment. Once you have all your formulas in place you tell goal seek to set your last cell eg B40 to 0 by changing the monthly payment cell eg D3. There is probably a function that can do it but i know nothing about those functions.

 

[MickG]

I should think:-
25,000,000 x ((.07/12) +1) ^6
then use that result in the PMT function.

 

[BrutalLogiC]

PMT? https://exceljet.net/formula/calculate-payment-for-a-loan

I came across this earlier when trying to work it out but can't see how this lets me enter no payment months 1-6 then payments only months 7-38?

 

[BrutalLogiC]

Use goal seek. I presume you want equal payments each month save the first 6 months. Im no finance man but id have thought you need to have a column of month numbers then a formula next to them similar to:

=B8+(B8*7%/12)-$D$3

The first 6 months just dont include the -$D$3

=B2+(B2*7%/12)

where D3 is to house the monthly payment. Once you have all your formulas in place you tell goal seek to set your last cell eg B40 to 0 by changing the monthly payment cell eg D3. There is probably a function that can do it but i know nothing about those functions.

hi Steve I don't want to make equal payments, month 7 payment will be highest and month 38 will be lowest

 

[steve the fish]

Then there is a myriad of solutions and we cant really help.

 

[joeu2004]

I want to take out a loan for 25,000,000 at 7% annual fixed interest rate and make month end monthly repayments.
[....]
Month 0 = 25,000,000
Month 1 = 25,145,833
Month 2 = month 1 value + interest
Month 3 = month 2 value + interest
Month 4 = month 3 value + interest
Month 5 = month 4 value + interest
Month 6 = month 5 value + interest
Month 7 = month 6 value + interest less my first repayment
Month 8 = month 7 value + interest less my second repayment
Start of month 39 = nil

So the loan balance accrues compounded interest for 6 months. Then you have 32 months to pay off the accrued balance.

=PMT(7%/12, 32, -FV(7%/12, 6, 0, -25000000))

Proof:

[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TH][/TH]
[TH]C[/TH]
[TH]D[/TH]
[TH]E[/TH]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: right"]per#[/TD]
[TD="align: right"]pmt[/TD]
[TD="align: right"]end bal[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]25,000,000.00[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]25,145,833.33[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]25,292,517.36[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"]25,440,057.05[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"]25,588,457.38[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"]25,737,723.38[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"]25,887,860.10[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]889,200.08[/TD]
[TD="align: right"]25,149,672.54[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]889,200.08[/TD]
[TD="align: right"]24,407,178.89[/TD]
[/TR]
[TR]
[TD="align: center"]

---

[/TD]
[TD="align: right"]

---

[/TD]
[TD="align: right"]

---

[/TD]
[TD="align: right"]

---

[/TD]
[/TR]
[TR]
[TD="align: center"]37[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]889,200.08[/TD]
[TD="align: right"]2,636,778.18[/TD]
[/TR]
[TR]
[TD="align: center"]38[/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]889,200.08[/TD]
[TD="align: right"]1,762,959.31[/TD]
[/TR]
[TR]
[TD="align: center"]39[/TD]
[TD="align: right"]37[/TD]
[TD="align: right"]889,200.08[/TD]
[TD="align: right"]884,043.16[/TD]
[/TR]
[TR]
[TD="align: center"]40[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]889,200.08[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
</tbody>[/TABLE]

Formulas:
A2: =PMT(7%/12, 32, -FV(7%/12, 6, 0, -25000000))
E3: =E2*(1+7%/12)-D3
D9: =$A$2
Copy D9 and E3 down their respective columns.

Note that calculations are not rounded. If you choose to round the formula in A2, the formula in D40 should be:

D40: =ROUND(E39*(1+7%/12), 2)

 

[shanerobert]

Interest-Only Loan Payment Calculation Formula


Increase the sum you get by the yearly loan fee. At that point partition by the quantity of installments every year. There are different approaches to land at that equivalent outcome. Model (utilizing a similar advance as above): $100,000 times .06 = $6,000 every time of intrigue.