Dollars And Nonsense

At 9:30 a.m. — the Senate Agriculture committee meets to mark up legislation that would boost regulation of the derivatives market.

At 10:30 a.m. — the Federal Communications Commission meets to discuss the national broadband plan.

At 2 p.m. — President Obama is scheduled to be interviewed on CNBC’s “Street Signs” program.

In Wednesday’s Washington Post

Key Senate Republicans change their tone on legislation to overhaul U.S. financial regulation, backing away from their harsh attacks against the bill and expressing optimism that a bipartisan deal on the measure could be reached. the shift comes as public anger against Wall Street remains noticeably high, underscored by the federal lawsuit filed against Goldman Sachs last week.

Derivatives 101: a primer on the complex financial instruments that would be subject to stricter oversight under financial regulatory reform legislation being considered by Congress.

The International Monetary Fund proposes taxes on the financial industry that seek to protect the global economy against future market crises and rein in “excess” profits and pay at major banks.

In Europe, regulators are turning up the heat on Goldman Sachs, as Britain’s Financial Services Authority launches a formal investigation into the firm’s London operations. the move comes as Goldman faces with a firestorm of protest over soured investments that inflicted hundreds of millions of dollars in losses on European banks.

The federal case against Goldman Sachs hinges on whether the government can prove that the firm deliberately misled its clients. Goldman says that its customers were big financial firms that were intimately familiar with the securities it was selling. the Securities and Exchange Commission says Goldman left out key details about the investments in question.

Goldman says that its first-quarter profit nearly doubled from a year ago, to $3.3 billion, but the blockbuster results fail to quell the controversy stemming from civil fraud charges filed by the government. during the earnings call, top Goldman executives mount their first live response to the allegations.

Proposed changes to the government’s marquee foreclosure prevention initiative may hurt efforts to help troubled homeowners and could increase the risk of fraud in the program, according to a report from a federal watchdog.

Google discloses data on how often it receives requests for private information on online users from governments, and the numbers show that the United States and Brazil rank at the top of the list in terms of queries.

Some economists and budget experts fear that the hyperpartisan atmosphere in Washington has limited options for dealing with ballooning government deficits, a trend symbolized by the emphatic rejection of the value-added tax.

Speaking of spending, Senate Budget Committee Chairman Kent Conrad (D-N.D.) has issued his own proposal for significantly reducing the deficit by 2015, and it envisions far tougher restraints on spending that those outlined in President Obama’s budget blueprint.

What we’re reading elsewhere

CDOs revisited: in the years before the 2008 market crash, collateralized debt obligations generated untold riches for Wall Street firms. Now, with Goldman Sachs facing charges over one particularly toxic CDO, some on the Street are wondering whether the wealth generated by these insanely complex financial instruments was worth the trouble.

Reassuring words: Hedge fund manager John a. Paulson seeks to reassure his investors that his firm — and their money — won’t be hurt by the government’s lawsuit against Goldman Sachs over the mortgage-related Abacus deal assembled at his firm’s request in 2007, the Wall Street Journal reports. Paulson has not been accused of any wrongdoing, but the Goldman case has put the billionaire’s business in the spotlight.

Fabulous Fab’s role: the Securities and Exchange Commission complaint against Goldman Sachs is notable for naming just one individual, a midlevel executive named Fabrice Tourre, according to the White Collar Watch column on the new York Times’ DealBook blog. in parsing the SEC’s filing, two legal experts conclude that Tourre was named because of his e-mail trail and because the timing of his bonus could reveal a lot about what the firm knew at the time Abacus was put together.

Goldman and Bear: The December 2006 decision by Goldman Sachs to start betting against the mortgage market helped hasten the collapse of two Bear Stearns hedge funds in 2007, an event that launched a broader unraveling on Wall Street, writes “House of Cards” author William D. Cohan in a posting on the Daily Beast.

Love-hate relationship: As Congress considers far-reaching legislation to eshape financial regulations, Democratic and Republican lawmakers have been rattling the tin cup on Wall Street in recent months, seeking campaign contributions at dozens of fund-raising events, according to the Wall Street Journal.

SEC targets banks over hiding risk: The Securities and Exchange Commission may issue new rules that bar financial firms from using accounting tricks to temporarily lower their debt right before earnings reports are released, according to the Wall Street Journal. the move comes in response to reports that more than a dozen major banks used accounting tactics similar to those employed by Lehman Brothers to hide its risk exposure before its 2008 collapse.

Managing the mayor’s money? Despite increasing scrutiny from an investigation into an alleged kickback scheme, former Obama administration autos czar Steven Rattner is helping build a new company to manage the vast fortune of new York Mayor Michael Bloomberg, the new York Times reports. Rattner’s attorneys have rejected any suggestion their client did anything wrong.

Not exactly Easy Street: For small businesses, building a living from government contract work is anything but the gravy train that many people think it is, according to the Wall Street Journal, which finds that dealing with Uncle Sam requires significant investments of time, money and patience.

Google getting into the travel biz? the search giant is negotiating a possible acquisition of privately held ITA Software, which makes programs that help Web sites such as Orbitz search airline reservation databases, according to Bloomberg News. the deal would help Google compete against rival Microsoft in the online travel arena.

Investor ire at Massey: several large public pension funds are taking aim at Massey Energy chief executive Don Blankenship in the wake of the deadly mine disaster earlier this month, the Wall Street Journal reports. the funds hope to strip Blankenship of his board chairmanship.

Also caught our eye

Bend it like FIFA: At soccer’s World Cup in South Africa later this year, one thing will be decidedly different: the ball. FIFA, the sport’s governing body, has blessed a high-tech version of the soccer ball that bears little resemblance to what players kicked around just twenty or thirty years ago, according to the Wall Street Journal.

Economic Agenda: April 21, 2010

I’m just your average joe middle class income. I need some software that tells me what to invest in, how much to invest and checks to make sure my financial house is in order. any recommendations?

I don’t believe you’ll find software that gives you investment advice, if you want to track your finances you can buy quicken or microsoft money, or use the free site for Mint:

You need to do your own research to determine your risk tolerance, etc, and at least list a set of savings goals you have. whether it’s short term savings, retirement, etc.

this entry was posted on Monday, February 8th, 2010 at 6:27 pm and is filed under personal financial planning. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

Where can I find some personal financial planning software? I just …

Today we will build a mortgage payment calculator using excel. But we will not build a boring excel sheet, we will build a mortgage calculator that is easy to play with.

A mortgage payment is a monthly installment that you pay towards a loan. Any mortgage loan will typically have,

• loan amount
• duration of the loan (also called as tenure of mortgage) in years
• interest rate (APR) per year

Given these 3 parameters, we can easily determine the monthly installment amount (this will be the same amount for all months during loan tenure)

We are going to use Excel’s form controls (more on this below) to build a mortgage payment calculator like this:

Why you should not be boring and use the form controls

A form control is a button or check box or scrollbar or some other click-able thing you see in Windows. Do you know that you can add the very same controls to Excel spreadsheet to make the it interactive?

• instead of asking a user to enter “yes” or “no” in a cell, you can ask them to click a check box.
• instead of taking “age” in a cell, you can use a scroll bar and set the values from 0 to 100.

This way of gathering inputs is more fun, engaging and interactive.

Now that you find form controls hot and attractive, lets proceed and make a house loan payment calculator.

How is mortgage payment calculated?

As I said above, any mortgage (or housing loan) will have 3 parts – loan amount (p), loan tenure (n) and annual interest rate (r).

Given the values of P, N and R, we can find the monthly payments using Excel’s PMT formula like this:

[related: PMT formula syntax and examples]

We are dividing interest rate (R) by 12 since R is annual interest rate and we make monthly payments.

We are multiplying loan duration (N) with 12 since we are going to make monthly payments.

Making the mortgage calculator in Excel

We will use scroll-bar controls to take numeric inputs required (P,N and R) for the payment calculation. And we feed these values to PMT formula to find the monthly installment amount.

Step 1: Add a Scroll-bar Control

We will use this scroll bar to take “loan amount” input. To keep it simple, we will ask users to enter input in ‘000s. So, if the loan is $120,000, the input should be 120.

First add a scroll-bar form control to your excel sheet. To do this go to Developer Ribbon > Insert > Scroll-bar Form Control in Excel 2007. in Excel 2003 use View > Toolbars > Forms and select Scroll-bar control. (related: enable developer toolbar in excel 2007)

Once selected, just add the control to spreadsheet by clicking anywhere.

Step 2: Set Properties for this Scroll-bar

To set the properties for the scrollbar control, right click on it and go to “format control” option. Now go to “Control” tab.

Here set minimum and maximum values for the scroll bar. To keep our model simple, just set minimum as 35 and maximum has 500.

Also, select a cell to link the scrollbar. When you do this, excel links the scroll bar to the selected cell. So whenever scroll bar is updated the cell gets updated too (and vice-a-versa). See this illustration:

Step 3: Add Remaining Scroll bars

Repeat the same steps for 2 other scroll bars. one for interest rate and one for loan tenure.

Make sure you set the minimum and maximum values in a reasonable range.

Step 4: Plug the values in to PMT formula

Now that the scroll bars are ready, just write the PMT formula. Assuming you have linked scroll bars like this:

• Loan amount in cell A1
• Interest rate in cell A2
• Loan tenure (years) in cell A3

The formula will be,

=PMT((A2/12)%,A3*12,A1)

Remember, PMT returns value in negative numbers (as it is the amount we need to pay, not get). But you can make it positive (for display purposes) by multiplying it with -1 like this = -PMT((A2/12)%,A3*12,A1)

Step 5: Play with your Model

Now your mortgage payment calculator is ready. You can play with it by testing various combinations and finding monthly payments. You can easily see what happens when you increase loan tenure or decrease interest rate.

Download Excel Mortgage Payment Calculator

Here is the excel mortgage payment calculator file. Download and play with it.

Bonus – Making an Amortization Schedule

You can easily extend this model to add an amortization schedule to see how much of each monthly payment is towards principal and how much is for interest.

• You can calculate principal portion for any month using PPMT formula like this =PPMT(R/12,M,N*12,P). here “M” is the month for which you want principal amount.
• You can calculate interest portion for any month using IPMT formula like this =IPMT(R/12,M,N*12,P).

Click here for help: PPMT formula, IPMT formula and Excel Financial Formulas.

Do you love form controls?

Do you use form controls in your spreadsheets? I find them pretty intuitive and use them wherever I can. I have made many complex spreadsheet models easy to understand and work with by just adding form controls. the beauty is that, they require no programming or anything. You just add them and link them to a cell.

What about you? Do you love form controls? Where do you use them most?

Learn more about Excel Form Controls:

• Make a comparison chart using scroll bars
• Create a check box in excel
• Make a donut bar chart using form controls
• More form control examples

Comments

I’m using 2003 and have added a scrollbar control. When i right click to format it, i dont get the same control tab to edit the parameters that i get when i right click over your scrollbar. the first four are the same though. Any suggestions?

Form controls are a brilliant way to produce an interactive spreadsheet. Scroll bars and check boxes / radio buttons tend to be what I use the most. the main drawback with them is that they cannot by modified by VBA code, which is why I frequently have to use the ActiveX versions of the controls.

A nice application of these controls is to create a chart based on the cells that update when (for example) the scroll bar control is used. this gives you a simple and effective visual way to show how a change in one or more variables can affect the results you are interested in.

Hank: did you add the ActiveX version of the scrollbar? the ActiveX controls work slightly differently, as they are designed to interface with VBA code. If you aren’t seeing the ‘Control’ tab, then you probably used ActiveX rather than the basic form control. I can’t remember the exact layout of the controls in 2003, but when you are selecting the control to add there should be two sections, labelled ‘Form Controls’ and ‘ActiveX Controls’. Make sure you add the scrollbar from the ‘Form Controls’ section (which should be the upper of the two sections, if memory serves me correctly…..)

Hank: just been checking up on the 2003 menus, and Jon Peltier mentions it in this post:
http://peltiertech.com/WordPress/forms-controls-and-activex-controls-in-excel/
When you opened the toolbar, you need the ‘Forms’ toolbar, rather than the ‘Control Toolbox’.

I have been looking around for some excel help on how principal repayments impact the schedule – interest savings (particularly on visual representation)

To clarify: I am trying to get a graphical illustration between
(i) Outstanding principal if I were to make no repayments vis-a-vis
(ii) Outstanding principal factoring repayments made

Can some one help me with this, probably we can add this to the existing work and make it complete ?

Andy-
That’s been driving me nuts for some time. It was a simple solution i would have never found! Thanks.

Great calculator. It has plenty of features for getting your calculations just right. the interface is a little tricky but it’s worth it.

I’m using the scroll bar for another use and I want to be able to adjust the incremental change by .1 instead of whole numbers. Is there a way to do this?

@Zach.. this is a limitation of scrollbar controls. one way to over come this is by using an intermediate helper cell. I have done that for interest rate in the above example. Download and see it for yourself.

Zach: as far as I can tell, scrollbars can only take integer values as their increment. However, if it’s an increment of 0.1 that you want, I would use the standard increment of 1 in the scrollbar settings, and then in the next cell divide that output by 10.

RSS feed for comments on this post. TrackBack URI

Leave a comment

If you have a question, please ask in the forums

Mortgage Payment Calculator – Mortgage Calculator using Microsoft …

by Jon Gray

Jon Gray was manager of planning and analysis at Xsigo Systems, a San Jose-based technology start-up. he was laid off in November 2008, after almost two years with the company. Previously, Mr. Gray, 34, spent seven years in various finance roles at Symantec Corp., a security software maker. he lives in Los Gatos, Calif.

I find that with all of my job opportunities, the first interview invariably takes place over the phone. There is at least one phone screening conversation, normally with the company’s HR department. Occasionally even two or three phone screens may take place. here, I’ll share two contrasting experiences I’ve had with phone screens.

The first experience was actually the second screen for the role in question. the first screen, with the company’s recruiter, went well. If the recruiter was any indication, I knew I would fit the company’s culture well. It was the second conversation, with the hiring manager, that I want to focus upon. from the outset the cultural fit once again seemed perfect. We then moved past the pleasantries and on to my background and experience. the manager was an expatriate, and thereby familiar with my M.B.a. program (always a bonus in the US). he liked the tenure of my analytical work and my long experience working across hierarchical and organizational lines. We then dug into my technical capacities. Not being one to fib or exaggerate, I had to be honest with him that my knowledge of one particular discipline that his group used heavily was unfortunately on the lighter side.

When the recruiter later called to inform me that the company wanted to move forward with in-person interviews, I was pleased but somewhat surprised. Not only did I suspect that my technical capacities were potentially light for this role, but I thought I detected some hesitation from him as well. nevertheless, we did move forward and I proceeded into a series of onsite interviews wherein what seemed like 75% of the lines of inquiry were of a highly technical nature. Frankly, it was a catastrophic round of interviews. I can only hope that we all only ever encounter a round like that once or twice in our careers.

The second experience worked a bit differently. After screens with two recruiters, I had a chat with the hiring manager. the manager’s demeanor, at least on that particular day, seemed a bit harried and stern. More importantly, however, we both quickly came to the conclusion that the specific skill they were searching for, I did not have in my possession. less than ten minutes into the conversation, we cordially ended the call. Then, about a month late, I was contacted by this same company for an entirely different role. This new role matched my mix of strategic and technical experience much better. Long story short, I interviewed in depth for this position and conversations are still headed in a positive direction.

So with the benefit of having both of these experiences, I can say that with the first company, I was seduced by a potential job offer, despite wondering whether I was sufficiently qualified. No doubt my ego played some part in thinking I should continue on and see whether I might talk my way in the door despite my reservations. by doing this, however, I ended up impacting my search momentum and optimism. I had spent many hours preparing for a strategic interview when the company was interested in a much more technical profile. I felt my share of guilt for having imposed needlessly on the group’s time. the wiser path was certainly the one I took with the second company. better to leave a good impression with the company by acknowledging quickly that the match is not there.

Readers, how do you deal with the phone screening process? Share your thoughts in the comments section.

Maneuvering the Phone Screen

John H. Weierman of Hatfield has joined Harleysville Savings Bank’s commercial lending team. Weierman has more than 30 years’ experience in the community bank industry where his expertise has been commercial lending to small and midmarket businesses. He is an elected Hatfield Borough Council official and has served his community in this capacity for the past 16 years. Weierman is a graduate of Wake Forest University in Winston-Salem, N.C., with a bachelor of science degree in business.

Lynn Levy has joined Harleysville Savings Bank’s management team and will serve as assistant vice president and branch manager of the bank’s Lansdale office. Levy, a graduate of Temple University, brings 34 years of community banking experience. during most of her prior career, she worked in the lower Montgomery County area. Levy has served the community in the capacities of executive director and on the board of directors of various chambers of commerce and other business organizations.

Pamela J. Garin of Allentown has been named The McCann School of Business and Technology’s director — career services, effective immediately. Garin will be responsible for establishing advisory boards for all curricula and student internships with local and regional businesses.

Before joining McCann, Garin was the director of institutional advancement for Historic Bethlehem Partnership for two years where she was responsible for the capital campaign, marketing and membership, as well as planning corporate and charity events.

Prior to that, Garin was the corporate support and marketing manager at PBS 39 from 2000-02 and from 2006-07. In 2002, Garin started the office of Business Development for the city of Allentown and merged those duties with similar ones for Lehigh County. Garin began her career in 1983 as a marketing and sales representative for Tarkett inc. she is a graduate of the Allentown Business School in 2000 with a specialized business degree in business administration/management & marketing.

Scott Gruber has been promoted to head of National Penn Bank’s corporate banking areas. He was an executive vice president and formerly president of the Central Region. He will be responsible for corporate/middle market, commercial real estate and small business administration lending groups. Gruber joined National Penn Bank in 1997 as vice president and regional commercial lending manager of the bank’s Lehigh Valley Division. He was named executive vice president in 2002. He was previously vice president of large corporate banking and middle marketing lending groups and manager of dealer development for CoreStates and its predecessor Meridian Bancorp, inc.

Curtis Dorwart has been named vocational marketing product manager by Mack Trucks inc. He will be responsible for the commercialization of new and existing vocational products. Previously he was a Mack product planning manager for the company’s low cab forward models and prior to that, he was a vehicle architect in the product development area. He joined Mack in 1984 and has a bachelor’s degree from Lafayette College.

Business players

(ARA) – many parents may be putting their children with disabilities at risk without even knowing it. Because families are so overwhelmed with the daily demands of childcare, they fail to plan for the financial future of their children.

“We have found more people than you can imagine struggling with this issue and calling us for help,” says bill Dougherty, assistant vice president of Massachusetts Mutual Life Insurance Company (MassMutual). “And with nearly 5 million children with disabilities in America, the need is growing significantly.”

The specialized financial planning these families need is so complicated, critical and in such great demand, companies like MassMutual have developed programs and expertise to address this growing issue.

“Families have literally lost tens of thousands of dollars because they didn’t know how to make a plan,” says Dougherty.

“Medical technology has made Herculean strides in helping children with special needs live longer, more productive lives. but by doing so, we have created a significant need – these children are outliving their parents and if families don’t plan, then everyone could suffer, especially siblings who may be left caring for their brothers and sisters,” he says.

Ann and David MacLaren of Fortville, Ind. have four children, three with special needs. they worked with specially trained Special Care Planners to develop a plan, and will rely on whole life insurance to fund a Supplemental Special Needs Trust that will pay for the care of their children.

“Term life insurance doesn’t last forever the way whole life does,” says David. “And the last couple of years have taught us you can’t rely on the stock market for your enduring needs. we want to ensure our children have access to financial resources that are stable and will build over the long run to fund the trust.”

“We feel a tremendous amount of confidence and relief now that we have our plan in place,” says Ann. “We have planned for who will care for our children, and how that care will be paid for.”

MassMutual has put hundreds of its agents nationwide through rigorous educational and training programs at the American College in Bryn Mar, Penn. Financial services professionals are fully trained in the financial, legal and emotional issues and work with special needs families through the company’s SpecialCare program.

A SpecialCare program gives special needs families access to information, specialists, financial products and services that can help improve the quality of life for people with disabilities and special needs.

A specialized team that includes an attorney, certified public accountant, social workers and caregivers is an indispensable part of planning for families who have children with disabilities. A Special Care Planner can help bring this team together in a way that makes sense for special needs families.

For more information about MassMutual’s SpecialCare program visit MassMutual’s SpecialCare resource center at massmutual.com/specialcare.

Courtesy of ARAcontent

The views expressed here are those from a third-party content provider, and not necessarily the views or recommendations of the Lexington Clipper-Herald.

Children with special needs face uncertain financial future