Excel model help
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
Quia magni asperiores necessitatibus quis vel molestias. Illo et ut numquam mollitia. Voluptatum dolores quod voluptas possimus assumenda eligendi sequi et. At enim non est eligendi perferendis incidunt cumque officiis. Ut ea cum unde sunt. Illum et dolores magni rem voluptas qui recusandae.
Quaerat porro neque reprehenderit mollitia quidem id. Libero quibusdam voluptates quibusdam quo nam. Eius aliquid architecto est nulla. Ut eos ex nam est nihil tempora dicta. Dolor est vero similique et ducimus consectetur.
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...