LinkBack URL
About LinkBacks
Hello,
How can i translate this into a formula: If grace period is 6 months, then start loan repayment at month 7?
Many thanks,
Hello,
How can i translate this into a formula: If grace period is 6 months, then start loan repayment at month 7?
Many thanks,
Hi
Have a look and see if that is what you are after.
Chris
Click my star if I helped Thanks
Thanks Chris...actually,the attached is more what i'm looking for; i need to fix the yellow formula
Hi
Have a look at this one is it closer to the mark.
Chris
Thanks but I don't see what you have changed in the formula and it's still not working.
The repayment should automatically start after the grace period (formula should take into account that the grace period may change)
=IF(B20=$B$3+1,$B$9:$M$9,0)
This formula in B21 is close enough but one thing is missing which is not allowing the fill right to function properly
can anyone help?
Hi rentb23,
Have a look at the attached and see if it works for you. I have added a helper row (9) which adds the grace period to the month number. It's then simply a matter of using an INDEX/MATCH formula to match the month number in the "payment" area (row 28) to the month number in the helper row, and return the corresponding principal.
Hope this helps.
Brendan.
__________________________________________________________________________________________________
Things to consider:
1) You can thank any poster by clicking the * at the left of a helpful post.
2) You can help to keep the forum tidy by marking your thread as "Solved", if it has been answered to your satisfaction.
3) Help us to help you, by uploading a sample workbook, showing the type of data you're dealing with, and clearly indicating what the results should be.
Hi
Another solution
=IFERROR(IF(B20<=$B$3,0,HLOOKUP((B20-$B$3),$B$8:$M$9,2,FALSE)),"")
Sample sheet is attached
Thanks BB and Prashan, that's great.. it works! but can you please explain the formulas you used in simple non-excel language to fully understand the rationale and be better able to apply it in the future. I understand bits of it but not the whole thing
For example why IFERROR and why HLOOKUP(B20-$B$3),etc.
Thanks again!
Thanks for the rep, rentb23.
I presume your query is more directed at Prashan than me, as I didn't use a HLOOKUP - I'm not a big fan of H(orizontal) or V(ertical) LOOKUPs, and am therefore not best placed to explain them.
Regarding the IFERROR - have a look at the attached. You'll note that in B29, there's now a nasty "#N/A" being returned, as I've removed the IFERROR part of the formula, and the formula says to match the "1" in B28 to the lookup array - cells B9 - M9 (which contain numbers 4-15). As Excel cannot find "1" in the range of cells it's searching for "1" in, (because there is no "1" between 4 & 15), it returns an #N/A (basically a "value Not Available" result.) I think it's probably fair enough to say that most of us who use Excel, and who are in a position to avoid returning #N/As, generally do so, and the IFERROR function allows us to do just that.
From Excel Help: IFERROR returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula.
The value I had initially specified to be returned if an error was found was nothing ("") (what is between the "" is what is returned, and there is nothing between the ""); and I've changed C29 & D29 to return "-" and "Grace Period", respectively. Have a look, and maybe try putting the IFERROR back into B29, with your own message - I find "doing" is a great way to learn. (Sometimes it's the only way!)
If you want an explanation of the rest of the formula I used (the INDEX/MATCH part), just let me know.
Glad to hear that I could help you.
HLOOKUP((G20-$B$3),$B$8:$M$9,2,FALSE))
if $B$3=4 then G20-$B$3 gives value 2
HLOOKUP horizontally serach for value 2 in the first row in the array ($B$8:$M$9) & gives corrosponding value in the row 2. if you need to search for exact value, you have to put FALSE & for approximate matching put TRUE.
Hope my explaination is clear to you. if not don't hesitate to ask further.
Hello
If you spend a little time to get the hang of using lookups you will find them very useful if you are
working with data a lot.
Prashantha has given you a good explanation above, and you will find that the help for building
function expressions in excel is also very good.
Thanks All. BB1972 yes can you please give an explanation of the rest of the formula you used (the INDEX/MATCH part)
Last edited by rentb23; 11-04-2012 at 10:36 AM.
Hello
I'm not sure why your wanting to use look up as well as index as they both do the job in this case.
I've had a go with the attachment below. The Loan Repay sheet now has the repayment schedule
as 3 columns in addition to the original + the grace period (mths).
On "AnotherSheet" Im extracting the payments from the LoanRepay sheet as I think your wanting.
The formula just uses the index function which seems to work OK.
Ive left out the error handling in the formulae as I got excel 2003, and without the IFERROR function
it makes the formula much longer.
HTH
Sure. The INDEX/MATCH combination basically looks for a value that you have input, and returns a corresponding value from a column of your choice. The syntax can be a bit awkward to get your head around at first, but remembering "What you want returned, what you're looking for, where you're looking for it" helped me. Starting small, with data I'm familiar with also helps (as I can generally spot whether the result is correct or not straight away.)Originally Posted by rentb23
Have a look at the attached - I imagine most people know the capital cities of Ireland, England & France. You can see that if you enter the name of the country you're searching for the capital of in F2, the capital city of that country is returned in H2. The formula in H2:
says return from B2:B4 ("INDEX(B2:B4,") if there is a match for F2 ("MATCH(F2,") in the search array A2:A4. The "0" at the end specifies that it must be an exact match.
Please Login or Register to view this content.
INDEX/MATCH is a very useful, versatile combination of formulas, which has many, many uses.
Hope this helps.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks