[Solved]Formula for royalty calculation with different rates and terms on various dates

[jackson_hollon]

Dear all,

I need a formula to calculate royalty. The payment has different rates every year and different terms begins on various dates. I need the formula can auto looks up the dates and assigns the royalty payments to that month.

I have attached the file so that you can see the result I want.
Royalty.xlsx
Thanks for the help.

[boopathiraja]

Hi,
in K3 and drag it across

Please Login or Register  to view this content.

[boopathiraja]

Hi Refer the attachment ,

Below the formula with ref corrected

Please Login or Register  to view this content.

[jackson_hollon]

boopathiraja,

Thanks fr the reply.

Your formula works except when the royalty period is not whole year. Please look at the result on row 4 and 6, they are 1 1/2 year and 3 1/2 year, the results became 2 years and 4 years, 6 months more than suppose to be.

efb-3.xlsx

Could you please take a look on my attachment? Thanks again

[boopathiraja]

Hi Try with the below formula,

Refer the attachment

Please Login or Register  to view this content.

[jackson_hollon]

boopathiraja


This is perfect. Thank you very much for the help.