Interest calculation

[hardgrafting]

I am trying to work out the interest rate of the below given cash flows.

I have a principal balance of 1000 and I am paying interest quarterly, at the dates where the 1 is denoted. The interest amount is the sum of the previous quarters cash flows.

So for example in the 30/09/2018 date, I will be able to pay interest of 120 + 10 (principal balance is 1000)

on the 31/12/2018 I will pay interest of 30+30+35 again principal is 1000.

The principal balance will also fall as will be making some principal payments, but just want to get the fundamental right for now.

How do I calculate the interest rate?

Thanks


Principal 1000

[TABLE="width: 170"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD="align: right"]30/08/2018[/TD]
[TD="align: right"]120[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]30/09/2018[/TD]
[TD="align: right"]10[/TD]
[TD] 1[/TD]
[/TR]
[TR]
[TD="align: right"]31/10/2018[/TD]
[TD="align: right"]30[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]30/11/2018[/TD]
[TD="align: right"]30[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]31/12/2018[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]31/01/2019[/TD]
[TD="align: right"]40[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]28/02/2019[/TD]
[TD="align: right"]45[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]31/03/2019[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]30/04/2019[/TD]
[TD="align: right"]55[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]31/05/2019[/TD]
[TD="align: right"]60[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]30/06/2019[/TD]
[TD="align: right"]65[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]31/07/2019[/TD]
[TD="align: right"]70[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]31/08/2019[/TD]
[TD="align: right"]75[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]30/09/2019[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]31/10/2019[/TD]
[TD="align: right"]85[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]30/11/2019[/TD]
[TD="align: right"]90[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]31/12/2019[/TD]
[TD="align: right"]95[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]31/01/2020[/TD]
[TD="align: right"]100[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[James006]

Hello,

Have you tried to use Excel's built-in function IRR() ...

Hope this will help