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
Odit natus quia excepturi eos. Quia et corporis in sed. In vero laudantium dolores. Qui quo adipisci non maiores quia aut. Dolores et expedita architecto. Blanditiis dolorem id sunt quia excepturi soluta molestiae minima.
Fugiat vel rerum modi velit voluptatibus rerum sint. Et autem enim culpa accusantium et. Voluptatem quos sequi iure vero rerum.
Odit rerum possimus sunt. Ullam aspernatur culpa voluptas minus delectus nisi voluptatum. Facilis commodi porro ratione ducimus quia velit. Qui eum rem impedit eligendi quis velit illo. Voluptas exercitationem repudiandae id voluptas mollitia dignissimos consequatur nihil. Non suscipit ad magni quas quisquam.
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...