SOLVED

an excel formula

%3CLINGO-SUB%20id%3D%22lingo-sub-1567180%22%20slang%3D%22en-US%22%3Ean%20excel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1567180%22%20slang%3D%22en-US%22%3E%3CP%3EIs%20there%20any%20way%20to%20find%20the%20foreclosure%20value%20of%20a%20loan%20on%20a%20given%20period%20from%20a%20loan%20repayment%20table%20(amt%20varies%20through%20months)%20through%20IRR%20method%20in%20a%20single%20formula%20or%20a%20combination%20of%20formulas%20(%20e.g.%3A%20PMT()%20function%20but%20this%20formula%20gives%20only%20on%20an%20equal%20repayment%20monthly.%20If%20the%20repayment%20differs%2C%20is%20there%20any%20other%20way%3F)%3C%2FP%3E%3CP%3EInputs%20will%20be%26nbsp%3B%20Principle%20amount%2C%20Rate%20%25(irr)%2CEM%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1567180%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1567429%22%20slang%3D%22en-US%22%3ERe%3A%20an%20excel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1567429%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F750136%22%20target%3D%22_blank%22%3E%40Viji_Ninan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20find%20the%20PV%20of%20the%20paid%20loan%20installment%3C%2FP%3E%3CP%3Ethen%20see%20how%20much%20is%20left%20from%20the%20loan%20and%20send%20it%20to%20the%20month%20needed.%3C%2FP%3E%3CP%3E%26nbsp%3Bthis%20would%20be%20the%20amount%20needed.%3C%2FP%3E%3CP%3Esee%20the%20file%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1569736%22%20slang%3D%22en-US%22%3ERe%3A%20an%20excel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1569736%22%20slang%3D%22en-US%22%3E%3CP%3ESir%2C%20Thank%20you%20the%20quick%20feedback%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20attaching%20an%20excel%20file%26nbsp%3B%20.%20Will%20it%20be%20possible%20to%20find%20the%20high%20lighted%20answer%20through%20single%20formula%20or%20group%20of%20combination%20of%20formulas%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1570039%22%20slang%3D%22en-US%22%3ERe%3A%20an%20excel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1570039%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F750136%22%20target%3D%22_blank%22%3E%40Viji_Ninan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20answer%20in%20month%206%20shouldn't%20always%20be%202350%20.%3C%2FP%3E%3CP%3EIt%20depends%20on%20the%20payments%20before%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eanyways%20the%20NPV%26nbsp%3B%20at%200%20or%20FV%20at%20month%2012%20should%20be%200%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1570116%22%20slang%3D%22en-US%22%3ERe%3A%20an%20excel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1570116%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F746148%22%20target%3D%22_blank%22%3E%40ramizassaf%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20the%20quick%20reply%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20question%20was%20also%20the%20same%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20the%20Monthly%20repayment%20is%20irregular%20(i.e%201st%20Month%20500%2C%202nd%20Month%2C%20475%2C%203rd%20Month%20300....)%20what%20will%20be%20the%20F.A%20for%20the%207th%26nbsp%3B%20Months%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFrom%20the%20table%20%2C%20since%20the%26nbsp%3B%20repayment%20is%20constant%20(i.e%20428%2F-)%20the%20F.A%20was%20arrived%20to%26nbsp%3B%202531%20for%20the%207th%20month.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%26nbsp%3B%20would%20we%20calculate%20with%20a%26nbsp%3B%20formula%20or%20series%20of%20formulas%20to%20arrive%20in%20a%20single%20cell%20%2C%20if%20the%20repayment%20is%20not%20constant.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3E%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-SUB%20id%3D%22lingo-sub-1570184%22%20slang%3D%22en-US%22%3ERe%3A%20an%20excel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1570184%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F750136%22%20target%3D%22_blank%22%3E%40Viji_Ninan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20find%20what%20you%20want%20in%20the%20orange%20cells%3C%2FP%3E%3CP%3Ebest%20of%20luck%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1570210%22%20slang%3D%22en-US%22%3ERe%3A%20an%20excel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1570210%22%20slang%3D%22en-US%22%3Ejust%20great.%20Thanks%20you%3CBR%20%2F%3E%3CBR%20%2F%3ERegards%2F%20Viji%3C%2FLINGO-BODY%3E
New Contributor

Is there any way to find the foreclosure value of a loan on a given period from a loan repayment table (amt varies through months) through IRR method in a single formula or a combination of formulas ( e.g.: PMT() function but this formula gives only on an equal repayment monthly. If the repayment differs, is there any other way?)

Inputs will be  Principle amount, Rate %(irr),EM

6 Replies
Best Response confirmed by Viji_Ninan (New Contributor)
Solution

@Viji_Ninan 

You can find the PV of the paid loan installment

then see how much is left from the loan and send it to the month needed.

 this would be the amount needed.

see the file

Sir, Thank you the quick feedback

 

I am attaching an excel file  . Will it be possible to find the high lighted answer through single formula or group of combination of formulas

 

Thanks

 

@Viji_Ninan 

 

The answer in month 6 shouldn't always be 2350 .

It depends on the payments before

 

anyways the NPV  at 0 or FV at month 12 should be 0

@Ramiz_Assaf 

 

 

Thanks for the quick reply

 

My question was also the same

 

If the Monthly repayment is irregular (i.e 1st Month 500, 2nd Month, 475, 3rd Month 300....) what will be the F.A for the 7th  Months

 

From the table , since the  repayment is constant (i.e 428/-) the F.A was arrived to  2531 for the 7th month.

 

How  would we calculate with a  formula or series of formulas to arrive in a single cell , if the repayment is not constant.

 

Regards

Thanks

 

 

 

 

@Viji_Ninan 

Please find what you want in the orange cells

best of luck

just great. Thanks you

Regards/ Viji