Problems with creating a Pro rate formula

%3CLINGO-SUB%20id%3D%22lingo-sub-2707709%22%20slang%3D%22en-US%22%3EProblems%20with%20creating%20a%20Pro%20rate%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2707709%22%20slang%3D%22en-US%22%3E%3CP%3ELet's%20say%20I%20am%20tracking%20an%20annual%20operation%20reserves%20in%20loan%20documents%2C%20but%20the%20contract%20will%20allow%20me%20to%20pro%20rate%20from%20the%20close%20of%20escrow%20to%20the%20end%20of%20the%20fiscal%20year%20(%20FY%20%3D%201%2F1%20-%2012%2F31)%20for%20the%20first%20initial%20year.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnnual%20Reserve%20%2440%2C000%3C%2FP%3E%3CP%3EClose%20of%20Escrow%208%2F18%2F2021%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat's%20the%20most%20efficient%20method%20to%20output%20the%20pro%20rate%20%24%20amount%20via%20formula%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2707709%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2713012%22%20slang%3D%22en-US%22%3ERe%3A%20Problems%20with%20creating%20a%20Pro%20rate%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2713012%22%20slang%3D%22en-US%22%3E%3CP%3EHI%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1120509%22%20target%3D%22_blank%22%3E%40domdel%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMostly%2C%20%3CSTRONG%3EYEARFRAC()%3C%2FSTRONG%3E%20function%20is%20used%20for%20the%20purpose%20of%20pro-rated%20calculations.%3C%2FP%3E%3CP%3EThe%20function%20of%20%3CSTRONG%3EYEARFRAC()%3C%2FSTRONG%3E%20calculates%20the%20fraction%20of%20the%20year%20represented%20by%20the%20number%20of%20whole%20days%20between%20two%20dates%20(the%20start_date%20and%20the%20end_date).%3C%2FP%3E%3CP%3EI%20hope%20this%20function%20will%20be%20helpful%20in%20your%20case%20as%20well.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22tauqeeracma_0-1630607909752.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F307710iE7A0D750E0D853CB%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22tauqeeracma_0-1630607909752.png%22%20alt%3D%22tauqeeracma_0-1630607909752.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20refer%20the%20attached%20sample%20calculation%20that%20tries%20to%20address%20your%20requirements.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20let%20me%20know%20if%20it%20works%20for%20you.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3ETauqeer%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Let's say I am tracking an annual operation reserves in loan documents, but the contract will allow me to pro rate from the close of escrow to the end of the fiscal year ( FY = 1/1 - 12/31) for the first initial year.

 

Annual Reserve $40,000

Close of Escrow 8/18/2021

 

What's the most efficient method to output the pro rate $ amount via formula? 

 

 

 

1 Reply

HI@domdel 

 

Mostly, YEARFRAC() function is used for the purpose of pro-rated calculations.

The function of YEARFRAC() calculates the fraction of the year represented by the number of whole days between two dates (the start_date and the end_date).

I hope this function will be helpful in your case as well.

tauqeeracma_0-1630607909752.png

 

Please refer the attached sample calculation that tries to address your requirements.

 

Please let me know if it works for you.

 

Thanks

Tauqeer