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
Explicabo optio qui saepe atque. Non vero nemo quibusdam autem ea dolore. Dolores ea velit ut reiciendis mollitia quas beatae minima.
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...