hi,
I am wanting to find a date in a range of dates and if its not there take the earlier date. This will then drive a sum offset indirect formula to pick up interest for the financial year.
Can anyone advise how to write this formula to find the exact date or one earlier? I would then question how I calculate total repayments since 30 June prior also?
<colgroup><col width="260" style="width: 195pt; mso-width-source: userset; mso-width-alt: 9508;">
<col width="71" style="width: 53pt; mso-width-source: userset; mso-width-alt: 2596;">
<col width="100" style="width: 75pt; mso-width-source: userset; mso-width-alt: 3657;">
<col width="67" style="width: 50pt; mso-width-source: userset; mso-width-alt: 2450;">
<col width="64" style="width: 48pt; mso-width-source: userset; mso-width-alt: 2340;">
<col width="73" style="width: 55pt; mso-width-source: userset; mso-width-alt: 2669;">
<col width="82" style="width: 62pt; mso-width-source: userset; mso-width-alt: 2998;">
<tbody>
</tbody>
I am wanting to find a date in a range of dates and if its not there take the earlier date. This will then drive a sum offset indirect formula to pick up interest for the financial year.
Can anyone advise how to write this formula to find the exact date or one earlier? I would then question how I calculate total repayments since 30 June prior also?
Loans | ||||||
Current Mth | 28/02/2017 | |||||
closest date or one immediately prior | 28/02/2017 | This formula not correct | ||||
Cell reference | Loan!A10 | ="Loan!"&VLOOKUP(B3,Loan!$A$9:$B$48,2,0) | This is ok | |||
Last twelve months | ||||||
Total Loan Repayments last 12mths | 142,279.02 | |||||
Loan Repayments since 30 June prior year | ||||||
Total principle remaining | Thanks Darren | |||||
TABLE | ||||||
Payment Date | Ref | Beginning Balance | Payment | Principal | Interest | Ending Balance |
30/11/2016 | A12 | 900,000.00 | 71,139.51 | 62,558.69 | 8,580.82 | 837,441.31 |
28/02/2017 | A13 | 837,441.31 | 71,139.51 | 58,749.97 | 12,389.54 | 778,691.34 |
31/05/2017 | A14 | 1,588,691.34 | 71,139.51 | 47,246.42 | 23,893.09 | 1,541,444.92 |
30/09/2017 | A15 | 1,631,444.92 | 71,139.51 | 39,116.56 | 32,022.95 | 1,592,328.36 |
30/11/2017 | A16 | 1,592,328.36 | 71,139.51 | 55,172.60 | 15,966.91 | 1,537,155.76 |
28/02/2018 | A17 | 1,537,155.76 | 71,139.51 | 48,398.03 | 22,741.48 | 1,488,757.73 |
31/05/2018 | A18 | 1,488,757.73 | 71,139.51 | 48,624.60 | 22,514.91 | 1,440,133.13 |
30/09/2018 | A19 | 1,440,133.13 | 71,139.50 | 42,257.93 | 28,881.57 | 1,397,875.20 |
30/11/2018 | A20 | 1,397,875.20 | 71,139.51 | 57,122.46 | 14,017.05 | 1,340,752.74 |
28/02/2019 | A21 | 1,340,752.74 | 71,139.50 | 51,303.71 | 19,835.79 | 1,289,449.03 |
31/05/2019 | A22 | 1,289,449.03 | 71,139.51 | 51,638.80 | 19,500.71 | 1,237,810.23 |
30/09/2019 | A23 | 1,237,810.23 | 71,139.51 | 46,315.48 | 24,824.03 | 1,191,494.75 |
30/11/2019 | A24 | 1,191,494.75 | 71,139.51 | 59,191.92 | 11,947.59 | 1,132,302.83 |
29/02/2020 | A25 | 1,132,302.83 | 71,139.51 | 54,201.50 | 16,938.01 | 1,078,101.33 |
31/05/2020 | A26 | 1,078,101.33 | 71,139.51 | 54,835.07 | 16,304.44 | 1,023,266.26 |
30/09/2020 | A27 | 1,023,266.26 | 71,139.50 | 50,618.11 | 20,521.39 | 972,648.15 |
30/11/2020 | A28 | 972,648.15 | 71,139.51 | 61,386.38 | 9,753.13 | 911,261.77 |