Hi

I want to create a formula to work out the capital advance value for each deal. We are a business where we finance vehicle loan and resell that loan to a bank. The value at which the bank buys our loan is called capital advance value.

Capital advance is based on a list of dates which are basically funding dates. Bank funds our deals twice a month on a pre agreed dates. We have funding dates agreed up till 2022. The capital advance calculation is based on funding dates. I have created a spread sheet to calculate Capital advance, which is working fine. But I want to create a formula that can do the same work like my spread sheet(attached). So if I have to work out Capital advance for 100 loans it can easily be done using a formula.

The arguments for this formula will be:
Monthly Payment
Term
Funding DateCAV Calculator.xlsx
First Payment Date
Last Payment Date