Share this content

Loan Schedule in Excel

Loan Schedule - Excel

Didn't find your answer?

Hi

Does anyone have a monthly loan schedule in excel which they can kindly share, with monthly principal payment being a fixed amount.

I found a few schedules online but all had total payment (principal + interest) being the fixed amount, and I could not figure out how to amend it to my requirement.

Thanks

Replies (18)

Please login or register to join the discussion.

avatar
By tltodman
08th Nov 2020 09:29

I have one which might help. Send me a message with your email address (don't think I can attach a file to message on here)

Sorry - just realised my fixed amount is capital and interest, so no use

Thanks (1)
Replying to tltodman:
avatar
By billy009
08th Nov 2020 19:26

Oh well, I'll keep my search on

Thanks (0)
Replying to tltodman:
avatar
By Paul Shoyemi
01st Nov 2021 18:44

Please cna you share one with me on [email protected]

Thanks

Thanks (0)
Psycho
By Wilson Philips
08th Nov 2020 09:41

Don’t be so lazy. It’s one of the simplest things to do on Excel. (After formatting negative numbers.)

Thanks (0)
Replying to Wilson Philips:
avatar
By paulwakefield1
09th Nov 2020 08:46

Not necessarily under FRS102. Variable interest rates can be a right pain.

Edit: Ignore - misread question. Although changes in cashflow for reasons other than changes in market rates remain a pain under FRS102.

Thanks (0)
Replying to Wilson Philips:
avatar
By billy009
08th Nov 2020 19:22

Well it may be simple for some, I could not get my head around it so I asked for help. I did find a few on google but all had Payment as the fixed amount and so is the case in Excel 365 templates.

Thanks (0)
Replying to billy009:
Psycho
By Wilson Philips
08th Nov 2020 19:35

My point was that where you have a fixed total payment the calculation of the interest element is not always straightforward (though not that difficult). Where the principal amount is fixed, the interest charge should be child’s play for an accountant.

Thanks (1)
avatar
By Jonno1
08th Nov 2020 12:04

@PMT function in excel?
Also do a google search and download one of the sample files that co0me up?

Thanks (1)
Replying to Jonno1:
Scalloway Castle
By scalloway
08th Nov 2020 12:19

PMT is used when there is a fixed payment of interest and capital. The OP has a fixed capital repayment with variable interest.

I don't have a proper template set up any more but the interest should be calculated as the opening balance for the period times the interest rate divided by the period. I always did it by day.

Calculating the balance is just simple arithetic.

Thanks (1)
Replying to scalloway:
Psycho
By Wilson Philips
08th Nov 2020 12:25

scalloway wrote:

the interest should be calculated as the opening balance for the period times the interest rate divided by the period.

On a daily basis, I agree. But if calculating monthly not necessarily. Depends on payment date.

Thanks (1)
Replying to scalloway:
Psycho
By Wilson Philips
08th Nov 2020 12:27

Duplicate

Thanks (0)
Replying to Jonno1:
avatar
By billy009
08th Nov 2020 19:25

I did google and even checked the Excel 365 templates but all I found was where the Payment is fixed and not Principal. Anyways I'll keep my search on, thanks for the reply

Thanks (0)
Replying to Jonno1:
avatar
By billy009
08th Nov 2020 19:25

I did google and even checked the Excel 365 templates but all I found was where the Payment is fixed and not Principal. Anyways I'll keep my search on, thanks for the reply

Thanks (0)
Scalloway Castle
By scalloway
08th Nov 2020 19:36

Assuming you know the interest rate, amount advanced and number of payments it is prettty simple. It was one of the first spreadsheets I created 40 years ago. You need these columns:

Balance
Principal instalment
Interest - Balance times interest rate (annual rate divided by 12) times days
Total payment

The instalment is deducted from the Balance each month.

If you can't get something PM me and I can knock one up.

Thanks (1)
avatar
By patrickcb
12th Nov 2020 09:44

Unless it's me being thick, isn't calculating the reducing monthly interest on a loan simply a kind of "sum-of-the-digits" exercise, using the IRR function in Excel? I've used this function several times with clients and have agreed to the balances on commercial loans to the penny.

Thanks (0)
Replying to patrickcb:
Scalloway Castle
By scalloway
12th Nov 2020 10:35

Have you used IRR to calculate loans where the capital amount is fixed and interest is added on top?

Thanks (0)
avatar
By Adrian Warne
12th Nov 2020 11:21

Balance
Principal instalment
Interest - Balance times interest rate (annual rate divided by 12) times days
Total payment

should this be

Interest - Balance times interest rate (annual rate divided by 365) times day

just saying.................

Thanks (1)
avatar
By djokhoo
16th Nov 2020 20:50

Good day,

I have not seen a template for equal principal payments on an amortized loan but I am aware that some loans are facilitated with equal principal payments although the customer will have to pay a higher interest up front-the banks will be happy to make money faster this way.
As some others have mentioned formulas that you can adopt in excel {very good of them}- if however you wish to avoid programming excel, I can point you to a couple of websites that will deliver equal principal payments.
Hope this helps.

https://www.calculatorsoup.com/calculators/financial/amortization-equal-...

https://financial-calculators.com/fixed-principal-payment-calculator

Thanks (0)
Share this content