So I have a question regarding modeling in excel. I can't figure out what function to use and it's probably really easy but I've never had any training, just what I've taught myself thus far. I have my loan amort schedule for 60 months. I have one cell that's an input where I put in the month where refinancing would occur. In another cell, I want it to display the debt due on the original note. I was going to see if there's an easier way than nesting a bunch of if functions for 60 cells. Can you select a range of if the value equals that month, use the corresponding ending loan balance?
Let's say the refi happens in month 18. Can I point to the range of cells in column A (month in amort schedule) and when it finds 18, lets just say A20, it will spit out C20 (which would be the ending balance)?
I know it's probably a pretty basic function but I appreciate the help. Thanks guys
Shouldn't you just be able to use a vlookup? Say cell A1 has the month you want the refi in. Cells A2:A61 have months 1-60 and cells C2:C61 have the balance of the loan during that month. Then do
=vlookup(A1,A2:C61,3,FALSE)
This will find the value in A1 in the range A2:A61 and return the corresponding value in the column C to the right.
Wow that was easy ha Thank you so much for that. I've been on excel for a few hours now building and by the time I got to figuring this out I had given up and just came on here to ask. You're a life saver. I ended up using the FV function but this works way better since it'll work for data tables that might not be a function of time value of money, etc. Thanks again
Sit porro qui quia natus modi eum. Voluptatem animi sit doloribus neque vel.
See All Comments - 100% Free
WSO depends on everyone being able to pitch in when they know something. Unlock with your email and get bonus: 6 financial modeling lessons free ($199 value)
or Unlock with your social account...